Combine conditions with AND in the Mysql ON clause

advertisements

I have the following query, that I use to filter rows based on software_id and level. I've put the conditions in the ON-Clause since I still want rows returned, where there are no corresponding rows in the JobadvertsSoftware Table.

SELECT `Jobadvert`.`id` FROM `jobadverts` AS `Jobadvert` 

LEFT JOIN `users` AS `User` ON (`Jobadvert`.`user_id` = `User`.`id`) 

LEFT JOIN `jobadverts_softwares` AS `JobadvertsSoftware_0` ON
(`Jobadvert`.`id` = 'JobadvertsSoftware_0.jobadvert_id' AND
(`JobadvertsSoftware_0`.`software_id` = '32' AND
`JobadvertsSoftware_0`.`level` IN ('1', 4))) 

WHERE `Jobadvert`.`active` = 1 AND `User`.`premium` = '1' AND
Jobadvert`.`department_id` = (5) 

GROUP BY `Jobadvert`.`id`

The problem is that it also returns JobadvertsSoftware-rows where level is e.g. 2 Again, if I put that in the WHERE clause it will filter out the rows where there are not JobadvertsSoftware which it shouldn't do. How can I tell MySQL to return all rows of Jobadvert, where the given software_id AND the level matches or are NULL?


Try this:

SELECT `Jobadvert`.`id`, `JobadvertsSoftware_0`.`level`
FROM `jobadverts` AS `Jobadvert` 

LEFT JOIN `users` AS `User` ON (`Jobadvert`.`user_id` = `User`.`id`) 

INNER JOIN `jobadverts_softwares` AS `JobadvertsSoftware_0` ON
(`Jobadvert`.`id` = 'JobadvertsSoftware_0.jobadvert_id' AND
(`JobadvertsSoftware_0`.`software_id` = '32' AND
`JobadvertsSoftware_0`.`level` IN ('1', 4))) 

WHERE `Jobadvert`.`active` = 1 AND `User`.`premium` = '1' AND
Jobadvert`.`department_id` = (5) 

GROUP BY `Jobadvert`.`id`

Saludos!