Need help with tsql syntax

• rhunt

  SSCertifiable

  Points: 5868

  More actions

  March 8, 2005 at 11:24 am

  #545957

  Howard, if you are getting more records from the IN comparison than the EXISTS, you may have nulls in your column.  This seems wierd since you are comparing key columns (or so I thought).

  Anyway, I suggest reading about IN and EXISTS in BOL.  Pay particular attention to how each handles NULLS.

• Todd Bateman-211485

  SSC Journeyman

  Points: 83

  More actions

  March 8, 2005 at 11:27 am

  #545961

  Also, your query is returning rows everytime the ID in test = an ID in test.  This will always evaluate to True.  You will get a row back for every row in Test.  Your subqueries should be returning ID's from Test1, Test2, and Test3

• hmbtx

  SSCarpal Tunnel

  Points: 4664

  More actions

  March 8, 2005 at 11:34 am

  #545967

  You said: Also, your query is returning rows everytime the ID in test = an ID in test.  This will always evaluate to True.  You will get a row back for every row in Test.  Your subqueries should be returning ID's from Test1, Test2, and Test3

  Actually I corrected the following:

  SELECT * FROM TEST

  WHERE ID IN (SELECT ID FROM TEST)

  OR ID IN (SELECT ID FROM TEST)

  OR ID IN (SELECT ID FROM TEST)

  to:

  SELECT account_number, Description_1 FROM Chart_Of_Accounts

  Where Inactive_Flag=1

  and Account_Number IN (SELECT Account_Number FROM Flexible_Budget)

  OR Account_Number IN (SELECT Account_Number FROM Check_JE_Line_Item)

  OR Account_Number IN (SELECT Account_Number FROM Year_To_Date)

  order by Chart_Of_Accounts.account_number

  This gives me a large incorrect count.

   

  Also,

  coalesce(b.accountNumber, c.accountNumber, d.accountNumber,'No Value') <> 'No Value' worked just fine except that it returns such a large amount of rows I don't understand what value would this be.

  Howard