Struggling to find the right answer for this so hopefully someone can help. It maybe so simple that I've overlooked something obvious and making it more difficult than I should be. I have two tables - titles [including titleIDs and titleNames], and groups [including groupIDs with title IDs they are associated with]. A titleID can have many groupIDs attached.
I'm trying to write a query that brings me back results of TitleIDs that match a criteria of groupIDs that have been selected.
So I've tried
SELECT * FROM titles INNER JOIN groups ON titles.titleID = groups.titleID WHERE
groups.groupID = 6 AND
groups.groupID = 24 AND
groups.groupID = 53
So I want to return results of only titles that are only associated with ALL these group IDS. The numbers will actually be replaced by what someone selects from a few tickboxes, but have hardcoded them in for purposes of this example.
I tried experimenting with a subquery but I couldn't get it to work, also I believe Subs can slow things down and I'm already going to be dealing with a lot of data.
The plan is for someone to select one or more groupIDs from a list and then return only results of Titles that are associated with all the GroupsIDs selected.
Any pointers, clues, advice on this would be really welcome. Thanks
You can do so by using in() for the group ids and matching the count of distinct groups foreach title,if 3 group ids provided so count for each title groups must be 3 so the title that has exactly these 3 groups will be returned
SELECT * FROM titles t
INNER JOIN groups g ON t.titleID = g.titleID
WHERE g.groupID IN(6,24,53)
GROUP BY t.titleID
HAVING COUNT(DISTINCT g.groupID) = 3
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments