March 8, 2005 at 11:24 am
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.
March 8, 2005 at 11:27 am
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
March 8, 2005 at 11:34 am
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
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply