Mysql表并联,MySQL：查询并联接两个表

I have two tables that I believe I want to JOIN. I'm very new to this and am not completely sure…

The first table is called venues with the variables id, slug, name, etc. The second table is venue_terms with the variables id, option, venue, value. The matching variables are obviously venues.id and venue_terms.venue.

What I want to do is query venue_terms for matching values and then SELECT * FROM venues that match.

I've been working with the following query, but haven't been able to get it to work. I know INTERSECT isn't the solution, but I'm nut sure which JOIN I should use.

SELECT venue

FROM venue_terms

WHERE `option` = '1' AND `value` = '10'

INTERSECT

SELECT venue

FROM venue_terms

WHERE `option` = '2' AND `value` = '4';

I want to match those venue_terms.venue to the venues table. Can someone point me in the right direction?

UPDATE: To clarify, I'm trying to search multiple option/value combinations that ultimately have the same venue.id's. Basically, I want to able to find all of the venues where (option = 1 and value = 4) AND (option = 2 and value = 10) AND etc… where all of these are true.

解决方案

You want to find venues that match conditions in two rows in table venue_terms. This can be accomplished by various methods. The most usual is by joining that table twice (another would be by a grouping query).

Here's the first way. Join twice to the venue_terms table:

SELECT v.id --- whatever columns you need

, v.slug --- from the venues table

, v.name

FROM venues AS v

INNER JOIN venue_terms AS vt1

ON vt1.venue = v.id

INNER JOIN venue_terms AS vt2

ON vt2.venue = v.id

WHERE ( vt1.option = 1 AND vt1.value = 10 )

AND ( vt2.option = 2 AND vt2.value = 4 ) ;

If you have 3 conditions, join thrice. If you have 10 conditions, join 10 times. It would be good for the efficiency of the query to have a compound index on (option, value, venue) in the terms table.