Query - Filter out records based on criteria 4

[jtrembla]

Here is my table

DrugId | BrandGeneric | Coutnry
=============================================
1 brand US
1 brand France
1 generic US
1 generic France
1 brand germany

My goal is to return all branded drugs that do not have a generic in the same country,


hence the last record would be returned because there is no generic drug in germany.

 

[RiverGuy]

SELECT a.*
FROM MyTable a
WHERE BrandGeneric = 'brand'
AND NOT EXISTS 
	(SELECT 1 
	FROM MyTable b 
	WHERE a.DrugID = b.DrugID 
	AND a.Country = b.Country 
	AND b.BrandGeneric = 'generic')

 

[SQLSister]

You do this with a self join. Something like (not tested):

select t1.* from table t1
left join table t2 on t1.drugid = t2.drugid and t1.BrandGeneric <> t2.BrandGeneric 
where t2.drugid is null

"NOTHING is more important in a database than integrity." ESquared

 

[jtrembla]

Does this also work?

SELECT PricingDrugID, BrandGeneric, Country
FROM Table1
WHERE (((Table1.PricingDrugID) Not In (select PricingDrugID FROm Table1 where BrandGeneric = 'generic'))) OR (((Table1.Country) Not In (select Country FROm Table1 where BrandGeneric = 'generic')))


It seems to return the correct results, any guidance on which method is more efficient or acceptable?

 

[bborissov]

SELECT YourTable.*
       FROM YourTable
LEFT JOIN (SELECT DrugId, Country
                  FROM YourTabke
           WHERE BrandGeneric = 'generic') Tbl1
ON YourTable.DrugId  = Tbl1.DrugId
   YourTable.Country = Tbl1.Country
WHERE Tbl1.DrugId IS NULL

If I have no deja vu, I think I answered the same question a few days ago (can't remember how )

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

any guidance on which method is more efficient or acceptable?

This is difficult to say because it depends on various things, like indexes and statistics that may (or may not) exist on your table.

If I had to guess, I would say that boris's query will probably perform the best. But, more importantly, it's easy enough for you to determine. Here's how.

Open a query window (SQL Server Management Studio or Query Analyzer).

Copy/paste all code (multiple queries) in to the same query window.

If you are using SQL 2000's Query Analyzer, press CTRL-K on the keyboard. If you are using SQL 2005's SQL Server Management Studio, Press CTRL-M.

It will appear as though nothing has happened. However, when you run the queries (by pressing F5), it will run them all AND it will show you an Execution Plan tab. When you click this tab, you will see graphical information showing you the various steps SQL Server uses to execute the query. More importantly (for the sake of this discussion), you can compare the relative cost of each query, helping you to decide which query performs better.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jtrembla]

Yes, and thanks all, I am learning rather quickly because of this forum. You guys save lots of time.

 

[jtrembla]

bborissov, your code does not work. There seems to be an error with this...

ON YourTable.DrugId = Tbl1.DrugId
YourTable.Country = Tbl1.Country

 

[gmmastros]

he missed an AND.

[tt][blue]
ON YourTable.DrugId = Tbl1.DrugId
[!]AND[/!] YourTable.Country = Tbl1.Country [/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom