SQL - 使用JOIN“合并”3个表

I want to select users from a table depending from two relation tables

我想根据两个关系表从表中选择用户

The Structure:

[user]

uid | firstname | lastname | ...
---------------------------------
482 | USERa     | USERa    | ...
885 | USERb     | USERb    | ...
405 | USERc     | USERc    | ...
385 | USERd     | USERd    | ...

[news_info]

uid_local   | uid_foreign
--------------------------------
125         | 482       
100         | 405       

[news_add]

uid_local   | uid_foreign
--------------------------------
125         | 885   
105         | 385

Now i want to select only USERa and USERb via uid_local -> 125, from [news_info] and [news_add]

现在我想通过uid_local选择USERa和USERb - > 125,来自[news_info]和[news_add]

SELECT 
  nnfo.uid_local, 
  user.* 
FROM user
  JOIN news_info nnfo
    ON nnfo.uid_foreign = user.uid
WHERE nnfo.uid_local = 125

result = USERa // works

result = USERa //有效

SELECT 
  nadd.uid_local,
  user.* 
FROM user
  JOIN news_add nadd
    ON nadd.uid_foreign = user.uid
WHERE nadd.uid_local = 125

result = USERb // works

result = USERb //有效

now "merge" the sql statement into one...to get USERa and USERb

现在将sql语句“合并”为一个...以获取USERa和USERb

SELECT 
  nnfo.uid_local, 
  nadd.uid_local,
  user.* 
FROM user
  JOIN news_info nnfo
    ON nnfo.uid_foreign = user.uid
  JOIN news_add nadd
    ON nadd.uid_foreign = user.uid
WHERE nnfo.uid_local = 125 AND nadd.uid_local = 125

result = empty // no errors.....

result = empty //没有错误.....

what is wrong...i tryed some other statements but with no result :(

有什么不对...我尝试了一些其他的陈述,但没有结果:(

2 个解决方案

#1

When merging results of queries, there are 2 different approaches:

合并查询结果时,有两种不同的方法:

UNION
JOINs

To know which one to choose you have to know whether there's a technical link between tables in the 2 queries you wish to merge. In your case, there's a functional link (nnfo.uid_local = 125 AND nadd.uid_local = 125) but it's not enough to join these 2 universes. You still could get the result with only one SELECT, but I think the final result would be unclear and somehow not really in the spirit of what SQL provides.

要知道要选择哪一个,您必须知道要合并的2个查询中的表之间是否存在技术链接。在您的情况下,有一个功能链接(nnfo.uid_local = 125 AND nadd.uid_local = 125)但是加入这两个Universe是不够的。你仍然可以只用一个SELECT得到结果,但我认为最终的结果将不清楚,并且在某种程度上并不真正符合SQL提供的精神。

I'd go towards the UNION solution:

我会去UNION解决方案:

SELECT 
  nnfo.uid_local, 
  user.* 
FROM user
  JOIN news_info nnfo
    ON nnfo.uid_foreign = user.uid
WHERE nnfo.uid_local = 125 
UNION
SELECT 
  nadd.uid_local,
  user.* 
FROM user
  JOIN news_add nadd
    ON nadd.uid_foreign = user.uid
WHERE nadd.uid_local = 125

Edit: I see davek's result implements UNION ALL instead of UNION. I assumed you wished to return USERa only once if it happened to appear in both independant queries, that's why I used the simple UNION operator vs the UNION ALL that would return duplicate results as well.

编辑:我看到davek的结果实现了UNION ALL而不是UNION。我假设你只希望返回USERa一次,如果碰巧出现在两个独立的查询中,这就是为什么我使用简单的UNION运算符和UNION ALL也会返回重复结果的原因。

#2

Your uid_local IDs exist on different rows: so when you want to AND the results, you get nothing (as nothing intersects). You could do it with a union:

您的uid_local ID存在于不同的行上:因此,当您想要AND结果时,您什么也得不到(因为没有任何相交)。你可以用联盟做到这一点:

SELECT * from
(
SELECT 
  nnfo.uid_local, 
  user.firstname, user.lastname 
FROM user
  JOIN news_info nnfo
    ON nnfo.uid_foreign = user.uid
WHERE nnfo.uid_local = 125 

UNION ALL

SELECT 
  nadd.uid_local,
  user.firstname, user.lastname 
FROM user
  JOIN news_add nadd
    ON nadd.uid_foreign = user.uid
WHERE nadd.uid_local = 125
) x

#1