SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Parameter query for unmatched records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wsilage
Yak Posting Veteran

USA
74 Posts

Posted - 07/15/2013 :  07:37:59  Show Profile  Reply with Quote
I have to build a query and I am not sure how to build it.

Our data has providers with different products. We have an “DGH” product and a “PRM”. I need to find the provider that don’t have both. Its sounds very simple, but I am having an awful time figuring it out. Sometimes I get a record that has both products, it is probably how my where clause is working.
Anyway, here is an example of what I don’t want in my records because this is good. It has a DGH and PRM.
pro_pcs pro_lname pro_fname prop_prd pro_tax1 pro_addr100018791 Smith John DGH 12345868 Kiem ST
00018791 Smith John PRM 12345868 Keim ST


I would want to see this one because there only a DGH and it need to have a PRM product.

pro_pcs pro_lname pro_fname prop_prd pro_tax1 pro_addr1
00018791 Rubin Sam DGH 2345868 Kiem ST



I came up with this below and it seems to be working. The only thing is, I now have to make this so people can add a parameter on the pro_tax1 to see the different records. Is there a better way to do this or how can I use what I have below and add a parameter too it.



Select pro_pcs, pro_lname, pro_fname, pro_addr1
from #ALLProducts
Where pro_tax1 = '12345868'
and prop_prd = 'DGH'

except


Select pro_pcs, pro_lname, pro_fname, pro_addr1
from #ALLProducts
Where pro_tax1 = '12345868'and prop_prd = 'PRM'


James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 07/15/2013 :  07:44:57  Show Profile  Reply with Quote
Don't you also want to see those who do have PRM, but don't have DGH? If so, one way to write the query would be as follows. I have not added the where clause; if you need that, easy enough to add:
SELECT
	pro_pcs, pro_lname, pro_fname, pro_addr1
FROM
	#ALLProducts
GROUP BY
	pro_pcs, pro_lname, pro_fname, pro_addr1
HAVING 
	COUNT(DISTINCT prop_prd) < 2;
Go to Top of Page

wsilage
Yak Posting Veteran

USA
74 Posts

Posted - 07/15/2013 :  08:17:44  Show Profile  Reply with Quote
Thanks, no we don't need to know who has PRM and not DGH. PRM is the newer product and in our system we already have ones that have DGH in them.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 07/15/2013 :  08:28:09  Show Profile  Reply with Quote
quote:
Originally posted by wsilage

Thanks, no we don't need to know who has PRM and not DGH. PRM is the newer product and in our system we already have ones that have DGH in them.

If there are none who has PRM and not DGH, the query I posted would still work correctly.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000