Results 1 to 4 of 4

Thread: Distinct help

  1. #1
    Join Date
    Jan 2012
    Posts
    52

    Unanswered: Distinct help

    Hi guys I need som help selecting some values.
    This is what I have. I have 2 tables one called header, the other one called data.

    Both of them have 4 rows: Period, Company, Type, Number

    Both tables are being filled with information via a BCP script. And I have made a join in between this 2 tables in order to match Period, Company, Type and Number

    What I want to achieve is to display those ones who did not match while making the join i.e. which period, company, type, number from header does not match in data.

    Thanks for the help guys, been on this for a while and have gotten a headache

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's the logic you want to follow. Obviously use your own table names and join criteria
    Code:
    -- All matching records
    SELECT *
    FROM   t1
     INNER
      JOIN t2
        ON t2.id = t1.id
    
    -- Change to an outer join to include non-matching records from t2
    SELECT *
    FROM   t1
     LEFT
      JOIN t2
        ON t2.id = t1.id
    
    -- Note that the non-matching records from the last query show NULL for columns in t2
    --   Therefore we can identify those records in t1 that are not in t2 like this...
    SELECT *
    FROM   t1
     LEFT
      JOIN t2
        ON t2.id = t1.id
    WHERE  t2.id IS NULL
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2012
    Posts
    52
    thanks a lot

  4. #4
    Join Date
    Nov 2011
    Posts
    8
    The LEFT JOIN will show you the unmatched items from the first table. If you want all of the unmatched items from either table, change the LEFT JOIN to a FULL OUTER JOIN.

Posting Permissions

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