I have a table of Listings that has a many to many relationship with a Taxons table. The table structure looks like this:
listings
----------------
id (int)
name (varchar)
listings_taxons
----------------
listing_id (int)
taxon_id (int)
taxons
----------------
id (int)
name (varchar)
My goal is to select all rows in the listings
table that has a matching list of taxon ids. Each returned listing record must have a relationship with the two taxons, such that I get the record set containing the intersection of records between the two taxons.
Example: I have a listing called "Muffler" and it has the following taxons: "Ford", "Mustang", "Exhaust". If I query for all listings with "Ford" and "Exhaust" I should get all listings that have "Ford" and "Exhaust" as taxons.
How would I construct this query efficiently?
Best Answer
Subquery A will bring back all listing_ids that have Ford, Exhaust, or both. Doing the GROUP BY count within Subquery A gives any listing id that has a COUNT(1) of 2 has both Ford and Exhaust taxon ids becasue BB.listing_id would appears twice thus HAVING COUNT(1) = 2. Then Subquery A has an INNER JOIN with listings.
Make sure you have the following indexes
Here is some sample data
Here is it executed
Give it a Try !!!