I’m struggling to articulate what I’m trying to achieve.
I have a product which can be assigned many tags, I then have a subscription table which holds the productID and the tagID.
I’d like to search the subscription table in the most efficient way to see if a product is subscribed to ALL tags (not ANYthat I provide).
I’ve tried many things including this:
SELECT
productID
FROM productTagSubscription
WHERE tagID IN ( 'id1', 'id2', 'id3', 'id4');
So the product has to be subscribed to all 4 ID’s as 4 rows in the subscription table
This is then pulling back any records for ANY id, which isn’t what I’m trying to achieve – any advice appreciated!!
Table Structure:
Expected Output
My expected output when passing all 3 or 4 tagID’s into the table would give me a list of products that all subscribe to all tags provided.
You can count the diferent tagID for each product.
SELECT productID
FROM productTagSubscription
GROUP BY productID
HAVING COUNT(tagID) = 4; /* total amount of tags */
In many pivot tables, you can find a unique pair key, in this case between (productID, tagID), if that’s not the case make sure tagID are DISCTINCT.
HAVING COUNT(DISCTINCT(tagID)) = 4;
as long as I change the COUNT number to match the number of tags I’m looking for
If you want to avoid the number, assuming that all tags are been used in one or another product.
HAVING COUNT(DISCTINCT(tagID)) = (
SELECT MAX(DISCTINCT(tagID))
FROM productTagSubscription
);
HAVING COUNT(DISCTINCT(tagID)) = (
SELECT COUNT(*) FROM tags
);