Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Unanswered: Need help with sql query

    I have 2 tables:
    MasterTable
    =====
    Page
    ,Line
    ,Col
    ,Value

    and

    TempTable
    =====
    Page
    ,Line
    ,Col
    ,Value

    I need to write a query which will fetch a result for those Page/Line/Col in TempTable which has no match found in MasterTable.

    I was trying something like below:
    if exists (select *
    from MasterTable as mas
    right outer join TempTable as src
    on mas.Page=src.Page
    and mas.Line=src.Line
    and mas.Col=src.Col
    and mas.Page is null
    and mas.Line is null
    and mas.Col is null)
    begin
    print 'no match found'
    end

    how do we write a query when there are multiple attributes to equate on from both the tables. If it were a single column i would use a Not In operator.

    Please help.

    Thanks,
    Tushar

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, "rows in table1 that aren't in table2" can often be solved with NOT IN, except if you have a multi-column key -- row constructors were added in sql2008 but i'm not sure they work in the NOT IN scenario

    but "rows in table1 that aren't in table2" can also be solved with a LEFT OUTER JOIN and an IS NULL test
    Code:
    SELECT t.Page
         , t.Line
         , t.Col
         , t.Value
      FROM TempTable AS t
    LEFT OUTER
      JOIN MasterTable AS m
        ON m.Page = t.Page 
       AND m.Line = t.Line 
       AND m.Col  = t.Col  
     WHERE m.Page IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    SELECT t.Page
    , t.Line
    , t.Col
    , t.Value
    FROM TempTable AS t
    WHERE NOT EXISTS (select 1 from MasterTable WHERE Page = t.Page
    AND Line = t.Line
    AND Col = t.Col )

    and just try by using not in also
    as r937 suggested i may or may not work in this scenario

  4. #4
    Join Date
    Jan 2009
    Posts
    2
    Thanks to both your solution. It works. Much appreciate your response.

    Tushar

Posting Permissions

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