Search code examples
mysqldatabasemulti-table

MySQL query only returns results if both tables have rows

Problem: In my mysql database, I have two tables lists and shares. The lists table has all of the to do lists that users have created:

'lists' Table

The shares table stores all lists that have been shared from one user to another:

'shares' table

What I want to do is get all lists for a user, both the ones they've created as well as the shared ones they have access to. Currently, I have a select statement that works perfectly when there is an entry in the shares table (doesn't matter if it relates to the specific user I'm retrieving results for).

As soon as I remove the result from the shares table and try to use that same multi-table SELECT statement, I get no results and I can't figure out why.

Here's the SELECT statement that I'm using:

SELECT DISTINCT `lists`.* FROM `lists`,`shares` WHERE `lists`.user_id = '$userid' OR (`shares`.sharedwith_id = '$userid' AND `lists`.id = `shares`.list_id) ORDER BY `lists`.datecreated DESC

Any help with the query would be awesome. I've looked at JOINs but haven't seen anything that would be more efficient that what I have.

Solution

  • You really should be using JOIN instead of WHERE clause:

    SELECT DISTINCT `lists`.* 
FROM `lists`
LEFT JOIN `shares`
ON `lists`.`id`=`shares`.list_id AND `lists`.`user_id`=`shares`.`sharedwith_id`
WHERE `lists`.`id` = ?

    Finally, avoid SQL injection attacks: never embed arguments in your SQL. Pass your arguments as parameters.