SQL - Select from table，如果user_id为0，则从不同的表中选择

Question

我目前正在与SQL查询作斗争，我有2个表，clients和clientbook。

---

中clientbook表中总是有一个客户端条目:

id | firstname | lastname | user_id | saloon_id
-----------------------------------------------
1    lorem       ipsum      5         10
2    jack        inthebox   0         10

clients可能有也可能没有条目，这取决于客户端在预订时是否登录。如果clientbook.user_id为0，则客户端不在clients表中。clients看起来像这样:

user_id | firstname | lastname
------------------------------
5         lorem       ipsum   

---

的问题我想做如下查询:

如果clientbook.user_id不是0，则查询clients并从中选择clients.firstname和clients.lastname。

如果clientbook.user_id为0，则选择clientbook.firstname和clientbook.lastname。

---

我现在有什么

我有这样的查询:

SELECT 
c.firstname,
c.lastname
FROM
clients AS c
INNER JOIN
clientbook AS cb
ON
c.id = cb.user_id
WHERE
c.firstname LIKE CONCAT("%", 'lorem', "%")
AND
cb.saloon_id = 10

然而，这只会在clients中选择存在的行。如果没有行存在，它将不从clientbook中进行选择。

如何查询?

Answer 1

如果clientbook。User_id不为0，则查询客户端并选择客户端。名字和客户。姓氏从那里。

如果clientbook。User_id为0时，选择clientbook。

根据这个描述，您需要一个left join

select coalesce(c.firstname, cb.firstname) as firstname,
coalesce(c.lastname, cb.lastname) as lastname
from clientbook cb left join
clients c
on cb.user_id = c.user_id

Answer 2

create table clientbook(id int, firstname varchar(50), lastname varchar(50), user_id int, saloon_id int);
insert into clientbook values(1,    'lorem',  'ipsum',      5,         10);
insert into clientbook values(2,    'jack',   'inthebox',   0,         10);

create table clients (user_id int, firstname varchar(50), lastname varchar(50));
insert into clients values(5,         'lorem','ipsum'   );

查询:

SELECT 
(case when cb.user_id=0 then cb.firstname else c.firstname end)firstname,
(case when cb.user_id=0 then cb.lastname else c.lastname end)lastname
FROM
clientbook AS cb
Left JOIN
clients AS c
ON
c.user_id = cb.user_id

AND
cb.saloon_id = 10

输出:

lastname添加inthebox

我理解您想从clientbooks表中获取名称，如果您在客户端表上有匹配的行。为此，您可以将CASE和左连接组合起来。尽管您也可以创建子选择或联合所有查询来将它们合并在一起。详细信息是您应该通过"user_id"列连接它们。

SELECT ( CASE WHEN cb.user_id = 0 THEN cb.firstname ELSE c.firstname END ) AS firstname ,( CASE WHEN cb.user_id = 0 THEN cb.lastname ELSE c.lastname END ) AS lastname FROM clientbook AS cb LEFT JOIN clients AS c ON c.user_id = cb.user_id WHERE c.firstname LIKE CONCAT ( "%" ,'lorem' ,"%" ) AND cb.saloon_id = 10