php - SQL 多重连接结果为空

Question

我正在尝试为表“projects”上的搜索请求构建 SQL 查询。搜索还与与项目表有关系的其他表相关。

我尝试过:

SELECT projects.*

FROM projects
  LEFT JOIN documents         ON documents.projectID     = projects.id
  LEFT JOIN subdocuments      ON documents.id            = subdocuments.documentID
  LEFT JOIN subdocuments_tags ON subdocuments.id         = subdocuments_tags.subdocumentID
  JOIN tags                   ON subdocuments_tags.tagID = tags.id

WHERE (projects.name LIKE "%Test%" 
    OR projects.clientName LIKE "%Test%" 
    OR projects.description LIKE "%Test%" 
    OR projects.defaultTags LIKE "%Test%" 
    OR documents.name LIKE "%Test%" 
    OR subdocuments.name LIKE "%Test%" 
    OR documents.description LIKE "%Test%" 
    OR subdocuments.description LIKE "%Test%" 
    OR tags.name LIKE "%Test%")
AND (projects.hidden = 0 
    OR projects.ownerID = 2 
    OR projects_users.userID = 2)

GROUP BY projects.id

ORDER BY projects.updateTime DESC;

问题是，如果项目没有任何文档，即使没有 WHERE 子句，结果也始终为空。

Answer 1

@MatBailie

进一步讨论...

 DROP TABLE IF EXISTS i;

 DROP TABLE IF EXISTS table_a;

 CREATE TABLE ints (i INT NOT NULL PRIMARY KEY);

 INSERT INTO ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

 CREATE TABLE table_a (i INT NOT NULL,x CHAR(1) NOT NULL, PRIMARY KEY (i,x));

 INSERT INTO table_a VALUES
 (1,'a'),
 (1,'b'),
 (1,'c'),
 (1,'d'),
 (1,'e'),
 (2,'a'),
 (2,'b'),
 (2,'c'),
 (3,'a'),
 (3,'b'),
 (4,'a');

 SELECT * FROM ints;
 +---+
 | i |
 +---+
 | 0 |
 | 1 |
 | 2 |
 | 3 |
 | 4 |
 | 5 |
 | 6 |
 | 7 |
 | 8 |
 | 9 |
 +---+

 SELECT * FROM table_a;
 +---+---+
 | i | x |
 +---+---+
 | 1 | a |
 | 1 | b |
 | 1 | c |
 | 1 | d |
 | 1 | e |
 | 2 | a |
 | 2 | b |
 | 2 | c |
 | 3 | a |
 | 3 | b |
 | 4 | a |
 +---+---+

 SELECT m.* FROM ints m LEFT JOIN table_a n ON n.i = m.i WHERE n.x IN('c','d');
 +---+
 | i |
 +---+
 | 1 |
 | 1 |
 | 2 |
 +---+

 SELECT m.* FROM ints m JOIN table_a n ON n.i = m.i WHERE n.x IN('c','d');
 +---+
 | i |
 +---+
 | 1 |
 | 1 |
 | 2 |
 +---+

http://www.sqlfiddle.com/#!2/90c6ed/1