Help with query

[jtrembla]

I have a table with the following fields/records

GroupID GroupType DrugID BrandGeneric Country

1 E DrugZ Brand U.S.
2 E DrugZ Generic U.S.
3 E DrugZ Brand France
4 E DrugZ Brand Germany

I essentially need to pull back all Brand records that does not have a matching Generic record in the same country for a given drugid.

Thus, GroupID = 3 and 4 would return.

 

[bborissov]

SELECT YourTable.GroupId,
       YourTable.GroupType,
       YourTable.DrugId,
       YourTable.BrandGeneric,
       YourTable.Country
FROM YourTable
LEFT JOIN (SELECT Country, DrugId
                   FROM YourTable
           WHERE BrandGeneric = 'Generic') Tbl1
ON YourTable.Country = Tbl1.Country AND
   YourTable.DrugId  = Tbl1.DrugId
WHERE Tbl1.DrugId IS NULL

Not Tested!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.