I have tables with listings, categories and one that maps them to each other. So a listing can then be placed in multiple categories. Something like the following:
listings table
id
title
etc
categories table
id
categ开发者_运维技巧ory_name
etc
map table
listing_id
category_id
When I need to get all of the information for listings within a single category (in this case the category with the id of 18), The following works fine:
SELECT *
FROM (`listings`, `map`)
WHERE `map`.`category_id` = 18
AND map.listing_id = listings.id
My problem is how can I do a similar type of query, but now I need to find those distinct listings that are within two categories. For example, what if I need to return only those distinct listings that are in both category_id = 18 AND category_id = 20? Would this require some type of join?
Yes, you'll want to use (another) join. I think the following should do it:
SELECT lst.`id`, lst.<column>, ...
FROM `listings` lst, `map` m, `map` m2
WHERE m.`category_id` = 18 AND m2.`category_id` = 20
AND m.`listing_id` = lst.`id`
AND m2.`listing_id` = lst.`id`
Another version, inspired by Germ's suggestions but this one works (note I replaced id
with category_id
for clarity):
select l.listing_id
from listings l
join (select m.listing_id, count(*) as cnt from map m where
m.category_id in (18,20)
group by m.listing_id) cat_matches
on cat_matches.listing_id = l.listing_id
where cat_matches.cnt = 2; -- 2 is the number of distinct categories to match
Ugly, eh? And the subselect might not be all that efficient... but:
select l.listing_id
from listings l
join map m on l.listing_id=m.listing_id
where m.category_id in (18,20)
group by l.listing_id
having COUNT(*)=2;
You can eliminate that subselect by getting all the rows you need and then filtering them. Note that this solution assumes that the lines in the map table are unique (which should be the case as the PK should be defined on both listing_id
and category_id
).
This should work
select * from listings l
join map m on m.listing_id = l.id
join categories c on c.id = m.category_id
where c.id in (18, 20)
How about this...
select * from listings l, map m, categories c
where l.id = m.listing_id
and m.category_id = c.id
and (c.id = 18
or c.id = 20)
or
select * from listings l, map m, categories c
where l.id = m.listing_id
and m.category_id = c.id
and c.id in (18, 20)
精彩评论