Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
tariq2
Posting Yak Master
125 Posts |
Posted - 2011-04-07 : 09:37:37
|
I am trying to identify a list of BusinessUnitID which are in the first query but not the second. The below doesnt quite work?Thanks in advanceselect distinct BusinessUnitID from S_IS_BU b inner join BIProd.dbo.D_BusinessUnits c on b.BUKey = c.BUKey where c.Year = '2011'where not exists(select distinct BusinessUnitID from Upload_GeneralExpense ) |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-07 : 09:45:49
|
This will give you the BusinessUnitIDs from S_IS_BU (I am assuming that's where it comes from) that can be found in the intersection of S_IS_BU and BIProd.dbo.D_BusinessUnits based on the BUKey, but don't exist in the Upload_GeneralExpense tableselect distinct BusinessUnitIDfrom S_IS_BU b inner joinBIProd.dbo.D_BusinessUnits c on b.BUKey = c.BUKey where c.Year = '2011'and not exists(select * from Upload_GeneralExpense u where b.BusinessUnitID = u.BusinessUnitID )JimEveryday I learn something that somebody else already knew |
|
|
Devart
Posting Yak Master
102 Posts |
Posted - 2011-04-07 : 10:03:51
|
For example:SELECT DISTINCT BusinessUnitIDFROM S_IS_BU b INNER JOIN BIProd.dbo.D_BusinessUnits c ON b.BUKey = c.BUKey LEFT OUTER JOIN Upload_GeneralExpense u ON b.BusinessUnitID = u.BusinessUnitIDWHERE c.Year = '2011' AND u.BusinessUnitID IS NULLDevart,SQL Server Tools:dbForge Data StudiodbForge Schema ComparedbForge Data ComparedbForge SQL Complete |
|
|
|
|
|