MySQL:查询并联接两个表 [英] MySQL: Query and join two tables

问题描述

我有两个表，我相信我想加入.我对此很陌生，还不确定………

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

第一个表称为 venues ，变量为 id，slug，name 等.第二个表为 venue_terms ，变量为 id，选项，地点，价值.匹配的变量显然是 venues.id 和site_terms.venue.

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.

我想做的是查询 venue_terms 匹配值，然后SELECT * FROM venues匹配.

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

我一直在使用以下查询，但无法使其正常工作.我知道INTERSECT不是解决方案，但是我不确定应该使用哪个JOIN.

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';

我想将这些place_terms.venue与 venues 表匹配.有人可以指出我正确的方向吗?

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

更新:为澄清起见，我正在尝试搜索最终具有相同place.id的多个选项/值组合.基本上，我希望能够找到所有(选项= 1且值= 4)和(选项= 2且值= 10)且等等…所有满足条件的场所.

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.

推荐答案

您想在表venue_terms的两行中找到与条件匹配的venues.这可以通过各种方法来实现.最常见的方法是将该表连接两次(另一种方法是通过分组查询).

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).

这是第一种方法.两次加入venue_terms表:

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 ) ;

如果您有3个条件，请加入三次.如果您有10个条件，请加入10次.在条件表中的(option, value, venue)上具有复合索引对于查询的效率而言将是有益的.

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.

这篇关于MySQL:查询并联接两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！