Help with Query

[jtrembla]

I am having a tough time on where to begin with this...

PricingDrugGroupID | PricingDrugName | Country | BrandGeneric
===========================================================================
1 PricingDrug A USA BRAND
2 PricingDrug A France BRAND
3 PricingDrug A Japan BRAND
4 PricingDrug B USA BRAND
5 PricingDrug B Germany BRAND
6 PricingDrug B France BRAND
7 PricingDrug B Japan BRAND
8 PricingDrug C Germany BRAND


=============
MyGeography
=============

Country
--------
USA
France
Japan
Germany


What I need

I want to return the PricingDrugNames that do not have all of the country records listed in the MyGeography table.

Thus,

PricingDrug A, PricingDrug C

 

[RiverGuy]

Try this. You can copy and paste this as is into a query window. When you're ready to test it against your data, swap out the table variable names with your actual table names.

DECLARE @PricingDrugs TABLE (
PricingDrugGroupID INT,
PricingDrugName VARCHAR(50),
Country VARCHAR(50),
BrandGeneric VARCHAR(50)
)

DECLARE @MyGeography TABLE (
Country VARCHAR(50) )


INSERT INTO @PricingDrugs SELECT 1,                         'PricingDrug A',           'USA',               'BRAND'
INSERT INTO @PricingDrugs SELECT 2,                         'PricingDrug A',           'France',            'BRAND'
INSERT INTO @PricingDrugs SELECT 3,                         'PricingDrug A',           'Japan',             'BRAND'
INSERT INTO @PricingDrugs SELECT 4,                         'PricingDrug B',          'USA',               'BRAND'
INSERT INTO @PricingDrugs SELECT 5,                         'PricingDrug B',           'Germany',           'BRAND'
INSERT INTO @PricingDrugs SELECT 6,                         'PricingDrug B',           'France',            'BRAND'
INSERT INTO @PricingDrugs SELECT 7,                         'PricingDrug B',           'Japan',             'BRAND'
INSERT INTO @PricingDrugs SELECT 8,                         'PricingDrug C',           'Germany',           'BRAND'

INSERT INTO @MyGeography SELECT 'USA'
INSERT INTO @MyGeography SELECT 'France'
INSERT INTO @MyGeography SELECT 'Japan'
INSERT INTO @MyGeography SELECT 'Germany'


SELECT DISTINCT PricingDrugName
FROM
	(SELECT x.*, y.Country AS yCountry
	FROM
		(SELECT p.PricingDrugName, g.Country
		FROM
			(SELECT DISTINCT PricingDrugName
			FROM @PricingDrugs) p
		CROSS JOIN @MyGeography g) x
	LEFT OUTER JOIN @PricingDrugs y ON x.PricingDrugName = y.PricingDrugName AND x.Country = y.Country
	WHERE y.Country IS NULL) z