Sql Server Programming :: Identifying Vendors with no activity


	Identifying Vendors with no activity I am trying to identify Vendors with no activity whatsoever My current scripts is trying to check for Vendors from the Vendor table (Vendor.vendid) and making sure that I can isolate all Vendors who have no presence in the APdoc table. (Apdoc.Vendid) select vendor.vendid,vendor.* from Vendor left outer join APDoc on Vendor.VendID = APDoc.VendID Where APDoc.VendID Is NULL I now need to check if these Vnedors are in the Purchord.Vendid table as well. Therefore I need to get a list of all Vendor.Vendid where they have no presence in the APdoc.Vendid or Purchord.Vendid tables. I would really appreciate any help on this. Thanks Sam Commar Before someone tells you to post your table structure and sample data, use the BOL to read up on NOT IN. "Sam" <s_com@hotmail.com> wrote in message news:1180445868.894998.131280@g4g2000hsf.googlegroups.com... - Hide quoted text - >I am trying to identify Vendors with no activity whatsoever > My current scripts is trying to check for Vendors from the Vendor > table (Vendor.vendid) and making sure that I can isolate all Vendors > who have no presence in the APdoc table. (Apdoc.Vendid) > select vendor.vendid,vendor.* from Vendor left outer join APDoc on > Vendor.VendID = APDoc.VendID Where APDoc.VendID Is NULL > I now need to check if these Vnedors are in the Purchord.Vendid table > as well. > Therefore I need to get a list of all Vendor.Vendid where they have > no presence in the APdoc.Vendid or Purchord.Vendid tables. > I would really appreciate any help on this. > Thanks > Sam Commar Try: select * from vendor v where not exists ( select * from APDoc a where a.VendID = v.VendID ) and not exists ( select * from Purchord p where p.VendID = p.VendID ) -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Sam" <s_com@hotmail.com> wrote in message news:1180445868.894998.131280@g4g2000hsf.googlegroups.com... I am trying to identify Vendors with no activity whatsoever My current scripts is trying to check for Vendors from the Vendor table (Vendor.vendid) and making sure that I can isolate all Vendors who have no presence in the APdoc table. (Apdoc.Vendid) select vendor.vendid,vendor.* from Vendor left outer join APDoc on Vendor.VendID = APDoc.VendID Where APDoc.VendID Is NULL I now need to check if these Vnedors are in the Purchord.Vendid table as well. Therefore I need to get a list of all Vendor.Vendid where they have no presence in the APdoc.Vendid or Purchord.Vendid tables. I would really appreciate any help on this. Thanks Sam Commar -----------------------------------------------Reply----------------------------------------------- On May 29, 9:55 am, "Tom Moreau" <t@dont.spam.me.cips.ca> wrote: - Hide quoted text - > Try: > select > * > from > vendor v > where not exists > ( > select > * > from > APDoc a > where > a.VendID = v.VendID > ) > and not exists > ( > select > * > from > Purchord p > where > p.VendID = p.VendID > ) > -- > Tom > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau > "Sam" <s_com@hotmail.com> wrote in message > news:1180445868.894998.131280@g4g2000hsf.googlegroups.com... > I am trying to identify Vendors with no activity whatsoever > My current scripts is trying to check for Vendors from the Vendor > table (Vendor.vendid) and making sure that I can isolate all Vendors > who have no presence in the APdoc table. (Apdoc.Vendid) > select vendor.vendid,vendor.* from Vendor left outer join APDoc on > Vendor.VendID = APDoc.VendID Where APDoc.VendID Is NULL > I now need to check if these Vnedors are in the Purchord.Vendid table > as well. > Therefore I need to get a list of all Vendor.Vendid where they have > no presence in the APdoc.Vendid or Purchord.Vendid tables. > I would really appreciate any help on this. > Thanks > Sam Commar Tom Thanks a ton.