SQL multiple join with empty results

Question

I am trying to build a SQL Query for a search request on the table "projects". The search is also related to other Tables that has a relationship to the projects table.

I tried:

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;

The issue is that if the projects don't have any documents and the result is always empty even without a WHERE clause.

JOIN tags ON is creating the issue, try it to be as LEFT JOIN — Abhik Chakraborty
– Abhik Chakraborty, Commented Apr 24, 2014 at 8:32
There is a inner join which may be the problem, if you can, make it a left join. — Nil'z
– Nil'z, Commented Apr 24, 2014 at 8:37
There are no aggregating functions in your query so GROUP BY is probably not doing whatever it is you think it's doing... and where did projects_users come from? Also, I think those LIKEs could only work that way if they were in a HAVING clause. — Strawberry
– Strawberry, Commented Apr 24, 2014 at 8:39
@Strawberry - It's actually valid and correct in MySQL. If you GROUP BY a unique key (usually primary key) it becomes functionally the same as grouping by every field in that table. It's very powerful, but causes merry-confusion when accidentally mis-used (especially by people new to SQL). — MatBailie
– MatBailie, Commented Apr 24, 2014 at 8:46
@MatBailie Actually, in this instance - yes - because we're only selecting from the projects folder - which makes a mockery of the LEFT JOINs!!! (At least until they're rendered as INNER JOINs by virtue of the WHERE clause!!!!) But we obviously cannot see the full query. — Strawberry
– Strawberry, Commented Apr 24, 2014 at 8:47

Strawberry · Accepted Answer · 2014-04-24 09:02:36Z

1

@MatBailie

For further discussion...

 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

answered Apr 24, 2014 at 9:02

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Strawberry Over a year ago

I see (sort of) - I don't think I've seen that implementation before! Good example BTW. But it seems like a hack because it relies on the "project.name = ... OR ..." construct. Is there another way of writing it?

MatBailie Over a year ago

I wouldn't call it a hack. The only bit I consider unusual is the GROUP BY id instead of DISTINCT or GROUP BY id, name. An alternative could be WHERE name LIKE '%test%' OR EXISTS (<correlated sub-query>) but in the OP's case that would get long winded due to the 4 related tables; (The first correlated query referencing just the first table, the second correlated query needing to join two tables, the third correlated query needing to join three tables, etc, etc). I actually think the OP's way of doing it is correct (once the JOIN is changed to LEFT JOIN).

MatBailie Over a year ago

Are any of the first three here more familiar / less hacky structures to you? sqlfiddle.com/#!2/f51875/9

Strawberry Over a year ago

No - they're all equally "hacky" ;-) But, fair enough.

MatBailie Over a year ago

How would you do it? Taking in to account that the OP's question checks for the existence of '%test%' in multiple places, yields the four LEFT JOINs there?

|

Strawberry · Accepted Answer · 2014-04-24 09:14:17Z

As Mentioned above in the comments use a LEFT JOIN to include rows from the projects table even if there are no matching rows in the documents, subdocuments or tags tables

 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
 LEFT 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;

Collectives™ on Stack Overflow

SQL multiple join with empty results

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related