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!!
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
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 );