Find Common Data

nbryson · December 18th, 2003, 06:55 AM

I have a product table and a product format table joined by a link table as you can have multiple product formats.

I have a stored procedure which I receive product ids and store them in a temp table. I want to return the common product formats between these products passed in.

I could do with some ideas to kick start my brain this morning.

Nickie

Jeff Mason · December 18th, 2003, 11:37 AM

Perhaps I don't understand your requirement; won't a SELECT DISTINCT on the JOIN of the temp and the link do the job?

Code:

SELECT DISTINCT Format
FROM #temp INNER JOIN ProductsAndFormats
    ON #temp.ProductID=ProductsAndFormats.ProductID;

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

nbryson · December 18th, 2003, 11:50 AM

That would give me all of the product formats and I am only looking for the common ones. e.g.

Say I had the following data
Prod1 with formats A, B, C, D
Prod2 with formats B, D, E, G
Prod3 with formats A, B, C, D

I am looking for B and D to be returned as they are common to the temp tables products.

Nickie

Jeff Mason · December 18th, 2003, 02:34 PM

If I understand you correctly, what you are looking to do is called 'relational division'.

I'll assume a 'ProductsAndFormats' table which is a linking table containing a Product Code/ID column called 'Product' and another column called 'Format' (assuming foreign key constraints to their respective tables). There is another table called 'Temp' which lists just Product codes/ID, i.e. 'Product':

Code:

SELECT PF1.Format
    FROM ProductsAndFormats AS PF1 INNER JOIN Temp T1
        ON PF1.Product=T1.Product
GROUP BY PF1.Format
HAVING COUNT(PF1.Product) = (SELECT COUNT(Product) FROM Temp);

(I think :D)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

nbryson · December 19th, 2003, 05:25 AM

Your assumptions were correct and the SQL returns exactly what I am looking for.

Thanks a lot. \o/