Parameter query for unmatched records

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Parameter query for unmatched records

Author

Topic

wsilage
Yak Posting Veteran

82 Posts

Posted - 2013-07-15 : 07:37:59

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-15 : 07:44:57

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;

wsilage
Yak Posting Veteran

82 Posts

Posted - 2013-07-15 : 08:17:44

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-15 : 08:28:09

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources