mySQL 如何在使用多个联接时通过特定 id 限制查询

Question

我有以下查询:

SELECT u.id, name AS user_name, phone, site_name, site_description, monitor_id, user_id, computer_id, site_id, computer_name, computer_description
FROM users AS u
JOIN
(
SELECT s.id, name AS site_name, description AS site_description, computer_id, user_id, computer_id, site_id, computer_name, computer_description
FROM sites AS s
JOIN
(
SELECT monitor_id, user_id, computer_id, site_id, computer_name, computer_description
FROM service_log AS sl 
JOIN 
(
SELECT id, site_id, name AS computer_name, description AS computer_description
FROM computers) 
AS c ON sl.computer_id = m.id) 
AS tab1 ON tab1.site_id = s.id) 
AS tab2 ON u.id = tab2.user_id

这工作正常，并按预期输出一个列表，如下所示:

id | user_name | phone | site_name | site_description | monitor_id | user_id | computer_id | site_id | computer_name | computer_description
 1 | john      | 12345 | home      | Johns house      |     1      | 234     | 123         | 22      | asd           | Tandy TRS-80 

所以现在我想通过 id 来限制结果。我认为我可以通过在底部的第二个选择中添加 WHERE 子句来做到这一点，例如通过 computer_id 进行限制，如下所示:

(
SELECT monitor_id, user_id, computer_id, site_id, computer_name, computer_description
FROM service_log AS sl 
WHERE computer_id  IN ('123')   //ADDITIONAL CLAUSE HERE
JOIN 
(
SELECT id, site_id, name AS computer_name, description AS computer_description
FROM computers) 
AS c ON sl.computer_id = m.id) 
AS tab1 ON tab1.site_id = s.id) 
AS tab2 ON u.id = tab2.user_id

但这行不通。但是，如果我在没有像这样的连接的情况下运行该特定语句:

   SELECT monitor_id, user_id, computer_id, 
   FROM service_log AS sl 
   WHERE computer_id  IN ('123') 

它可以正常工作，没有任何问题。我执行连接的方式一定是出了问题。谁能帮我吗？

Answer 1

SELECT u.id, name AS user_name, phone, site_name, site_description, monitor_id, user_id, computer_id, site_id, computer_name, computer_description
FROM users AS u
JOIN
(
SELECT s.id, name AS site_name, description AS site_description, computer_id, user_id, computer_id, site_id, computer_name, computer_description
FROM sites AS s
JOIN
(
SELECT monitor_id, user_id, computer_id, site_id, computer_name, computer_description
FROM service_log AS sl 
JOIN 
(
   SELECT id, site_id, name AS computer_name, description AS computer_description
   FROM computers 
       #additional clause here
   WHERE id  IN ('123')
) 
AS c ON sl.computer_id = m.id) 
AS tab1 ON tab1.site_id = s.id) 
AS tab2 ON u.id = tab2.user_id