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.