SELECT DISTINCT w.ID, w.WCltID, w.WCltNum, w.WempID, w.Wpdate, w.WEmpLname FROM WIP w INNER JOIN CltBudget c ON w.WCltID<>c.CBudCltID WHERE w.Wpdate >= '12/18/2007' AND w.WcodeSub NOT IN ('IT', 'MKT', 'ABRA','GEN', 'DR', 'MGT') AND w.WCltNum <> '99999'
Ok, the statement above works like 50%. I'm trying to query two different tables in the same DB. I want the records where w.WCltID (in the WIP table) <> c.CBudCltID (in the CltBudget table). It is returning some of the right records but I'd say half of them are incorrect. Many of the records returned exist in w.WCltID and c.CBudCltID, I don't want those records.
Any suggestions?
Thanks for your help!
The <> does not seem to work too well. You might try this:
sql Code:
 Original
- sql Code |
|
|
SELECT DISTINCT
w.ID, w.WCltID, w.WCltNum, w.WempID, w.Wpdate, w.WEmpLname
FROM WIP w
LEFT JOIN CltBudget c ON w.WCltID = c.CBudCltID
WHERE w.Wpdate >= '12/18/2007' AND w.WcodeSub NOT IN ('IT', 'MKT', 'ABRA','GEN', 'DR', 'MGT') AND w.WCltNum <> '99999' AND c.CBudCtlID IS NULL
This returns records in WIP where there is no match in Cltbudget. Is that what you wanted? If so, give that a try and let me know if it works for ya!
Was this answer helpful ?
Yes No
Quote:
| Originally Posted by ncozzolino SELECT DISTINCT w.ID, w.WCltID, w.WCltNum, w.WempID, w.Wpdate, w.WEmpLname FROM WIP w INNER JOIN CltBudget c ON w.WCltID<>c.CBudCltID WHERE w.Wpdate >= '12/18/2007' AND w.WcodeSub NOT IN ('IT', 'MKT', 'ABRA','GEN', 'DR', 'MGT') AND w.WCltNum <> '99999'
Ok, the statement above works like 50%. I'm trying to query two different tables in the same DB. I want the records where w.WCltID (in the WIP table) <> c.CBudCltID (in the CltBudget table). It is returning some of the right records but I'd say half of them are incorrect. Many of the records returned exist in w.WCltID and c.CBudCltID, I don't want those records.
Any suggestions?
Thanks for your help! |
May not be the most efficient query, but how about
:
SQL Code:
Original
- SQL Code |
|
|
SELECT DISTINCT ID, WCltID, WCltNum, WempID, Wpdate, WEmpLname
FROM WIP
WHERE WctlID NOT IN (SELECT DISTINCT CBudCltID FROM CltBudget)
AND Wpdate >= '12/18/2007'
AND WcodeSub NOT IN ('IT', 'MKT', 'ABRA','GEN', 'DR', 'MGT')
AND WCltNum <> '99999'
Was this answer helpful ?
Yes No
Quote:
| Originally Posted by Lauramc The <> does not seem to work too well. You might try this:
sql Code:
Original
- sql Code |
|
|
SELECT DISTINCT w.ID, w.WCltID, w.WCltNum, w.WempID, w.Wpdate, w.WEmpLname FROM WIP w LEFT JOIN CltBudget c ON w.WCltID = c.CBudCltID WHERE w.Wpdate >= '12/18/2007' AND w.WcodeSub NOT IN ('IT', 'MKT', 'ABRA','GEN', 'DR', 'MGT') AND w.WCltNum <> '99999' AND c.CBudCtlID IS NULL
This returns records in WIP where there is no match in Cltbudget. Is that what you wanted? If so, give that a try and let me know if it works for ya! |
YOU ARE THE BEST! It worked!! Thank you for responding so quickly!!!!!!
Was this answer helpful ?
Yes No
Are there any tutorials on advanced SQL Statements?
Was this answer helpful ?
Yes No
I use SQL Team sometimes when I have a tough question....
SQL Team
Also if you use the Books Online in SQL Server that can help too. Feel free to stop on by if you have more questions
Was this answer helpful ?
Yes No
