none
Joining tables

    Question

  • I am struggling with this for a while and your help will be very much appreciated. Not sure how to describe it in words so sharing this with data

    TableA (include from this table per ID)

    id                     Value    Desc

    1                       23        'Text1'

    1                       24        'Text2'

    1                       25        'Text3'

    2                      31         'Text4'

    2                      32        'Text5'

    3                      132       'Text6'

    TableB (exclude form this table per id)

    1                       344

    1                       323

    2                      5

    2                      6

    3                      149

    3                      150

    TableC

    23                    'Text1'

    24                    'Text2'

    25                    'Text3'

    31                     'Text4'

    32                    'Text5'

    344                  'Text6'

    323                  'Text7'

    5                      'Text8'

    6                      'Text9'

    I am joining these three tables with many other tables and result is rows like these

    1           23        --------------------------------

    1           24        --------------------------------

    1           344      --------------------------------

    2          31         --------------------------------

    2          5          --------------------------------

    3          149       --------------------------------

    3          150       --------------------------------

    I want to select only rows like this (note that rows containing matching Ids are excluded)

    1           23        --------------------------------

    1           24        --------------------------------

    2          31         --------------------------------

    Friday, February 01, 2013 5:05 PM

All replies

  • Use EXCEPT to exclude the records from tableB:

    Here is an example:

    select * from (
    --Your query with columns: id, value 
    values (1, 23),(1,24), (1,344),(2,31),(2,5), (3,150))  d(c1,v1)
    EXCEPT
    select * from (
    --tableB with columns id, calue
    values (1, 323),  (1,344),(2,6),(2,5), (3,149), (3,150))  d(c1,v1)
    

    Friday, February 01, 2013 5:37 PM
  • I am sorry for not mentioning this in my original post. I am need to use function such as  sum based on the exclusion. 

    The query would be like sum(id), so I can not use except. 

    Thanks


    Friday, February 01, 2013 5:46 PM
  • Use a LEFT JOIN instead:

    select d1.id,d1.val from (
    --Your query with columns: id, value 
    values (1, 23),(1,24), (1,344),(2,31),(2,5), (3,150))  d1(id,val)
    
    LEFT JOIN (
    --tableB with id, calue
    values (1, 323),  (1,344),(2,6),(2,5), (3,149), (3,150))  d2(id,val) ON d1.id=d2.id AND d1.val=d2.val
    
    WHERE d2.id IS NULL ANd d2.val is null
    

    Friday, February 01, 2013 5:51 PM
  • You can use the above as derived table and perform SUM on the remaining rows.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, February 01, 2013 5:58 PM