I have a table called BR_writing that contains writing submissions.
I have a table called BR_categories with a list of categories such as Fantasy, Science Fiction, Anime, etc.
I then have another table called BR_writing_to_cat of just ids to link the two together. IE: writing_id, category_id
Normally, if I wanted to, say, select all entries in the Science Fiction category, it would be something like:
SELECT W.writing_id, title, summary
FROM BR_writing W, BR_writing_to_cat C
WHERE W.writing_id = C.writing_id AND category_id = '".$scifi_id."'
LIMIT 0, 10
But what if I want to select all writing entries in Anime AND Fantasy? Below is the query I have now, but it seems like there might be a better or more optimal one out there.
SELECT writing_id, title, summary
FROM BR_writing_to_cat A, BR_writing W
INNER JOIN BR_writing_to_cat B
ON A.writing_id = B.writing_id
AND B.category_id = ".$id1."
WHERE A.writing_id = W.id AND A.category_id = ".$id2."
If I wanted to do three at once, i’d do:
SELECT writing_id, title, summary
FROM BR_writing_to_cat A, BR_writing W
INNER JOIN BR_writing_to_cat B
ON A.writing_id = B.writing_id
AND B.category_id = ".$id1."
INNER JOIN BR_writing_to_cat C
ON B.writing_id = C.writing_id
AND C.cat_id = ".$id2."
WHERE A.writing_id = W.id AND A.category_id = ".$id3."
I’m wondering if there’s a faster query than this, and one that I could scale to maybe 5 category selections at once. Thanks for your help!