如何在MySQL中使用多个表从单列中选择多个值？

I have the following 3 tables

我有以下3个表

table1

id    name
----------
1     john
2     dave
3     eve

table2

table1_id    table3_id
----------------------
   1            2            
   1            3
   1            5
   2            2
   2            3
   2            4
   3            1
   3            5

table3

id    title
------------
1     blue
2     orange
3     green
4     yellow
5     black

I'm trying to select each table1.name where table3.title = (blue OR orange) AND (green OR black)

我正在尝试选择每个table1.name其中table3.title =(蓝色或橙色)AND(绿色或黑色)

so that the output is

所以输出是

name
----
john
dave

This is my query so far that won't get this job done:

到目前为止,这是我的查询,不会完成这项工作:

SELECT table1.name 
FROM table1 
JOIN table2 ON table1.id = table2.table1_id
JOIN table3 t1 ON table2.table3_id = t1.id
JOIN table3 t2 ON t1.title = t2.title
WHERE t1.title IN ('blue', 'orange')
AND t2.title IN ('green', 'black')

Any suggestions would be really appreciated!

任何建议将非常感谢!

UPDATE One more question :)

更新还有一个问题:)

How to select each table1.name where table3.title = ('green' AND 'orange' AND 'black' AND '...')

如何选择table3.title =('green'和'orange'和'black'AND'...')的每个table1.name

2 个解决方案

#1

If I'm getting your question right, you're close:

如果我的问题是对的,你就近了:

SELECT table1.name
FROM table1 
JOIN table2 t1_link ON table1.id = t1_link.table1_id
JOIN table3 t1_data ON t1_link.table3_id = t1_data.id AND t1_data.title in ('blue', 'orange')
JOIN table2 t2_link ON table1.id = t2_link.table1_id
JOIN table3 t2_data ON t2_link.table3_id = t2_data.id AND t2_data.title in ('green', 'black')

#2

Not sure if that fits your need but you might wanna try this:

不确定这是否符合您的需求,但您可能想尝试这个:

SELECT DISTINCT table1.name, table3.title
FROM table2
LEFT JOIN (table1, table3)
ON (table2.table1_id = table1.id AND table2.table3_id = table3.id)
WHERE table3.title = 'orange' OR table3.title = 'blue';

You might wanna get rid of the DISTINCT which right now just ensures that each name is shown only once.

您可能想要摆脱DISTINCT,现在只需确保每个名称只显示一次。

cu Roman

#1