CODEWITHSUNDEEP
mysqlnullleft-joinisnull
Karma Blackshaw
Karma Blackshaw

Reputation: 942

WHERE clause in LEFT JOIN query

So i wish to get all the the SSC positions and get the number of positions which are not yet occupied. However, i somehow got stuck in the where clause.

Here is my table structures:

Position

SSC

Student

Account

And here is my query

SELECT p.position_id, a.account_id FROM positions p LEFT JOIN ssc s ON s.position_id = p.position_id AND s.removed = 0 LEFT JOIN students stud ON stud.students_id = s.students_id LEFT JOIN accounts a ON a.account_id = stud.account_id WHERE p.user_level_id = 6 AND p.removed = 0

The actual result is this:

Result

Expected result to show only the fields which are NULL in account_id. However if I include AND a.account_id = NULL in the query, the result is empty. And if i ever change the query into:

SELECT p.position_id, a.account_id FROM positions p LEFT JOIN ssc s ON s.position_id = p.position_id AND s.removed = 0 LEFT JOIN students stud ON stud.students_id = s.students_id LEFT JOIN accounts a ON a.account_id = stud.account_id AND a.account_id = NULL WHERE p.user_level_id = 6 AND p.removed = 0

Then all the account ID becomes NULL

Upvotes: 0

Views: 46

Answers (1)

user3783243
user3783243

Reputation: 5224

None of them = the NULL value. Use IS NULL.

a.account_id IS NULL

You can read more about this here, https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html. You may prefer to use empty strings, or a default value of 0.

Upvotes: 3

Related Questions