Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    46

    Unanswered: Number of records shown are not matching.

    There is something I don’t understand. When I use join
    SELECT r.CHECK_NUMBER, i.orig_file
    from (AP_INVOICEDOCS i join AP_DETAIL_REG r on r.PAYABLE_ID= i.PAYABLE_ID)
    I am getting 76 orig_file records
    But when I do
    SELECT r.CHECK_NUMBER, i.orig_file
    from (AP_INVOICEDOCS i right outer join AP_DETAIL_REG r on r.PAYABLE_ID= i.PAYABLE_ID)
    I am showing only 8 records under i.orig_file column and I am not sure why. What I need is to get all the AP_INVOICEDOCS in the matching orig_file records.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm nearly certain you want a LEFT join... LEFT means to keep all the rows from the first table, RIGHT means keep all of the rows from the second table.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2013
    Posts
    46
    Yes I want to keep all the records from the right table and get the matching orig_file records, which should be more than 8 as my join statement shows.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @a TABLE (c INT PRIMARY KEY)
    DECLARE @b TABLE (d INT PRIMARY KEY)
    
    INSERT INTO @a VALUES (0), (1), (2)
    , (3), (4), (5), (6), (7), (8), (9)
    
    INSERT INTO @b SELECT 2 * c FROM @a
    
    SELECT *, 'INNER' FROM @a       INNER JOIN @b ON (c = d)
    SELECT *, 'LEFT'  FROM @a LEFT  OUTER JOIN @b ON (c = d)
    SELECT *, 'RIGHT' FROM @a RIGHT OUTER JOIN @b ON (c = d)
    SELECT *, 'FULL'  FROM @a FULL  OUTER JOIN @b ON (c = d)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •