MySQL 仅当字段不为空时才进行内部联接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5979508/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inner join only if a field is not empty
提问by Sumz
I could have done what I want with two request, but I want to make it with only one.
我可以用两个请求完成我想要的，但我只想用一个请求来完成。
In fact, I have 4 tables with :
事实上，我有 4 个表：
Table 1 : id, sub-id
Table 2 : id, sub-id
Table 3 : id, login
Table 4 : id, login
I make a request like that :
我提出这样的要求：
SELECT Table1.id, Table1.sub-id, Table2.id, Table2.sub-id,
Table3.login, Table4.login FROM Table1
INNER JOIN Table2 ON (Table1.id = Table2.id AND Table1.sub-id = Table2.sub-id)
INNER JOIN Table3 ON (Table3.id = Table1.id)
INNER JOIN Table4 ON (Table4.id = Table1.id)
WHERE Table1.id = "my_id" AND Table1.sub-id = "my_subid"
I want to join Table3only if idis not empty, if it is empty, I join Table4.
我想加入Table3只有当id不为空，如果它是空的，我加入Table4。
Do you have any ideas please ?
你有什么想法吗？
I've heard that a left join could help, but I'm not that accustomed to these keywords so ...?
我听说左连接会有所帮助，但我不太习惯这些关键字，所以...？
采纳答案by krtek
Like said in the other answers, you can use a left join. You can also add case statementto have only one logincolumn :
就像其他答案中所说的那样，您可以使用左连接。您还可以添加case 语句以仅包含一login列：
SELECT
Table1.id,
Table1.sub-id,
Table2.id,
Table2.sub-id,
CASE
WHEN Table3.id IS NOT NULL THEN Table3.login
ELSE Table4.login
END CASE AS login
FROM Table1
INNER JOIN Table2 ON (Table1.id = Table2.id AND Table1.sub-id = Table2.sub-id)
LEFT JOIN Table3 ON (Table3.id = Table1.id)
LEFT JOIN Table4 ON (Table4.id = Table1.id)
WHERE Table1.id = "my_id" AND Table1.sub-id = "my_subid"
回答by H?vard S
Use a left join:
使用左连接：
SELECT Table1.id, Table1.sub-id, Table2.id, Table2.sub-id,
Table3.login, Table4.login FROM Table1
INNER JOIN Table2 ON (Table1.id = Table2.id AND Table1.sub-id = Table2.sub-id)
LEFT JOIN Table3 ON (Table3.id = Table1.id)
LEFT JOIN Table4 ON (Table4.id = Table1.id)
WHERE Table1.id = "my_id" AND Table1.sub-id = "my_subid"
Then, rows from Table3 and Table4 will be joined if there is a match. Otherwise, these will be NULL. You can then check the results and use data from either Table3 if present, or Table4 otherwise.
然后，如果有匹配项，将连接来自 Table3 和 Table4 的行。否则，这些将是NULL。然后，您可以检查结果并使用表 3（如果存在）或表 4 中的数据。
回答by álvaro González
Try something like this:
尝试这样的事情：
SELECT Table1.id, Table1.sub-id, Table2.id, Table2.sub-id,
COALESCE(Table3.login, Table4.login) AS login
FROM Table1
INNER JOIN Table2 ON Table1.id = Table2.id AND Table1.sub-id = Table2.sub-id
LEFT JOIN Table3 ON Table3.id = Table1.id
LEFT JOIN Table4 ON Table4.id = Table1.id
WHERE Table1.id = 'my_id' AND Table1.sub-id = 'my_subid'
I've removed unnecesary parethensis to reduce clutter. Also, please note that the standard string delimiter in SQL is the single quote.
我已经删除了不必要的 parethensis 以减少混乱。另外，请注意 SQL 中的标准字符串分隔符是单引号。
回答by jimy
This might help:
这可能有帮助：
SELECT Table1.id, Table1.sub-id, Table2.id, Table2.sub-id,
Table3.login, Table4.login FROM Table1
INNER JOIN Table2 ON (Table1.id = Table2.id AND Table1.sub-id = Table2.sub-id)
LEFT JOIN Table3 ON (Table3.id = Table1.id)
LEFT JOIN Table4 ON (Table4.id = Table1.id)
WHERE Table1.id = "my_id" AND Table1.sub-id = "my_subid"
回答by ypercube??
If, for any table1.id, either onlytable3.idor onlytable4.idwill match, you can use this:
如果对于 any table1.id，仅匹配table3.id或仅table4.id匹配，则可以使用以下命令：
SELECT
Table1.id
, Table1.sub-id
, Table2.id
, Table2.sub-id
, COALESCE(Table3.login, Table4.login)
AS login
FROM Table1
INNER JOIN
Table2 ON (Table1.id = Table2.id
AND Table1.sub-id = Table2.sub-id)
LEFT JOIN
Table3 ON (Table3.id = Table1.id)
LEFT JOIN
Table4 ON (Table4.id = Table1.id)
WHERE Table1.id = "my_id"
AND Table1.sub-id = "my_subid"
