仅当字段不为空时才进行内连接

发布于 2024-11-06 19:05:23 字数 705 浏览 0 评论 0原文

我本可以通过两个请求来完成我想要的事情，但我只想通过一个请求来完成。

事实上，我有 4 个表：

Table 1 : id, sub-id
Table 2 : id, sub-id
Table 3 : id, login
Table 4 : id, login

我发出这样的请求：

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"

仅当 id 不为空时我才想加入 Table3，如果它为空，我加入 <代码>表4。

请问您有什么想法吗？

我听说左连接会有所帮助，但我不太习惯这些关键字，所以......？

原文

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 :

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 Table3 only if id is not empty, if it is empty, I join 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 ...?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

柠檬色的秋千 2024-11-13 19:05:23

正如其他答案中所述，您可以使用左连接。您还可以添加 case 语句以仅包含一个 <代码>登录栏：

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"

Like said in the other answers, you can use a left join. You can also add case statement to have only one login column :

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"

回复收藏 0 原文

硬不硬你别怂 2024-11-13 19:05:23

使用左连接：

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"

如果存在匹配，则 Table3 和 Table4 中的行将被连接。否则，这些将为NULL。然后，您可以检查结果并使用表 3（如果存在）或表 4（否则）中的数据。

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.

回复收藏 0 原文

花期渐远 2024-11-13 19:05:23

尝试这样的事情：

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'

我删除了不必要的括号以减少混乱。另请注意，SQL 中的标准字符串分隔符是单引号。

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.

回复收藏 0 原文

梦开始←不甜 2024-11-13 19:05:23

如果对于任何 table1.id，仅 table3.id 或仅 table4.id< /code> 将匹配，你可以使用这个：

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"

If, for any table1.id, either only table3.id or only table4.id will match, you can use 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"

回复收藏 0 原文

つ可否回来 2024-11-13 19:05:23

这可能有帮助：

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"

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"

回复收藏 0 原文

~没有更多了~