将一个表多次连接到其他表

I have three tables:

我有三张桌子:

Table User( userid username)

表用户(userid用户名)

Table Key( userid keyid)

表键(userid keyid)

Table Laptop( userid laptopid)

表笔记本电脑(userid laptopid)

i want all users who have either a key or a laptop, or both. How do i write the query so that it uses a join between table User and table Key, as well as a join between table User and table Laptop?

我想要所有拥有钥匙或笔记本电脑或两者兼有的用户。如何编写查询以便它使用表User和表Key之间的连接,以及表User和table Laptop之间的连接?

The main problem is that in the actual scenario, there are twelve or so table joins, sth like:

主要问题是在实际场景中,有十二个左右的表连接,如:

" select .. From a left join b on (...), c join d on (..),e,f,g where ...",

“选择..从左边的连接b开始(...),c连接d on(..),e,f,g where ......”,

and i see that a could be joined to b, and a could also be joined to f. So assuming i can't make the tables a,b, and f appear side-by-side, how do i write the sql query?

我看到a可以加入b,a也可以加入f。所以假设我不能使表a,b和f并排出现,我该如何编写sql查询?

6 个解决方案

#1

You can use multiple joins to combine multiple tables:

您可以使用多个联接来组合多个表:

select *
from user u
left join key k on u.userid = k.userid
left join laptop l on l.userid = u.userid

A "left join" also finds users which do not have a key or a laptop. If you replace both with "inner join", it would find only users with a laptop and a key.

“左连接”还可以找到没有密钥或笔记本电脑的用户。如果用“内部联接”替换它们,它将只找到有笔记本电脑和密钥的用户。

When a "left join" does not find a row, it will return NULL in its fields. So you can select all users that have either a laptop or a key like this:

当“左连接”找不到行时,它将在其字段中返回NULL。因此,您可以选择所有拥有笔记本电脑或密钥的用户:

select *
from user u
left join key k on u.userid = k.userid
left join laptop l on l.userid = u.userid
where k.userid is not null or l.userid is not null

NULL is special, in that you compare it like "field is not null" instead of "field <> null".

NULL是特殊的,因为你比较它像“field is not null”而不是“field <> null”。

Added after your comment: say you have a table Mouse, that is related to Laptop, but not to User. You can join that like:

在你的评论后添加:说你有一个表鼠标,它与笔记本电脑有关,但不与用户有关。您可以加入以下内容:

select *
from user u
left join laptop l on l.userid = u.userid
left join mouse m on m.laptopid = l.laptopid

If this does not answer your question, you gotta clarify it some more.

如果这不能回答你的问题,你还需要澄清一下。

#2

-- // Assuming that a user can have at max 1 items of each type
SELECT      u.*
-- // Assuming that a user can have more then 1 items of each type, just add DISTINCT:
-- // SELECT      DISTINCT u.*
FROM        "User" u
LEFT JOIN   "Key"    u1 ON u.UserID = u1.UserID
LEFT JOIN   "Laptop" u2 ON u.UserID = u2.UserID
LEFT JOIN   "Server" u3 ON u.UserID = u3.UserID
-- // ...
WHERE       COALESCE(u1.UserID, u2.UserID, u3.UserID /*,...*/) IS NOT NULL

#3

As you described the case, you only wanted to know if someone has a laptop or a key. I would write the query with a subquery rather than a join:

正如您所描述的那样,您只想知道某人是否有笔记本电脑或钥匙。我会用子查询而不是连接来编写查询:

select * 
from user 
where userid in (select userid from key union select userid from laptop)

The reason for this is that by a join a person with multiple laptops or multiple keys will be listed several times (unless you use distinct). And even you use distinct you end up with a less efficient query (at least on Oracle the query optimizer doesn't appear to be able to create an efficient plan).

这样做的原因是,通过连接,具有多个笔记本电脑或多个键的人将被列出多次(除非您使用不同的)。即使您使用不同的最终查询效率较低(至少在Oracle上,查询优化器似乎无法创建有效的计划)。

[Edited to correct what Rashmi Pandit pointed out.]

[编辑纠正Rashmi Pandit指出的内容。]

#4

select distinct u.userid, u.username
from User u 
    left outer join Key     /* k on u.userid = k.userid */
    left outer join Laptop  /* l on u.userid = l.userid */
where k.userid is not null or l.userid is not null

EDIT "The main problem is that in the actual scenario, there are twelve or so table joins, sth like: " select .. From a left join b on (...), c join d on (..),e,f,g where ...", and i see that a could be joined to b, and a could also be joined to f. So assuming i can't make the tables a,b, and f appear side-by-side, how do i write the sql query?"

编辑“主要的问题是,在实际场景中,有十二个左右的表连接,如:”选择..从左边的连接b(...),c连接到(...),e, f,g where ...“,我看到a可以连接到b,a也可以连接到f。所以假设我不能使表a,b和f并排出现,我该如何编写sql查询?“

You can have as many left outer joins as required. Join the table with the primary key to the rest of the tables or on any other field where field values of one table should match field values of other table.

您可以根据需要使用多个左外连接。使用主键将表连接到其余表或任何其他字段,其中一个表的字段值应与其他表的字段值匹配。

eg will explain better than words

例如,将比文字更好地解释

select * 
from a
 left outer join b on a.pk = b.fk -- a pk should match b fk
 left outer join c on a.pk = c.fk -- a pk should match c fk
 left outer join d on c.pk = d.fk -- c pk should match d fk

and so on

等等

#5

Solution one:

SELECT * FROM [User] u
INNER JOIN [Key] k
ON u.userid = k.userid

UNION

SELECT * FROM [User] u
INNER JOIN Laptop l
ON u.userid = l.userid

[...]

Solution two:

SELECT * FROM [User] u
LEFT JOIN [Key] k
ON u.userid = k.userid
LEFT JOIN Laptop l
ON u.userid = l.userid
LEFT JOIN [...]
WHERE k.userid IS NOT NULL
OR l.userid IS NOT NULL
OR [...]

Just a guess, also you could check the execution plan for theses two to see if the UNION one is heavier or vice versa.

只是一个猜测,你也可以检查这两个的执行计划,看看UNION一个是否更重,反之亦然。

#6

 SELECT * 
 FROM User
 LEFT JOIN Key ON User.id = Key.user_id
 LEFT JOIN Laptop ON User.id = Laptop.user_id
 WHERE Key.id IS NOT NULL OR Laptop.id IS NOT NULL