Combine conditions with AND in the Mysql ON clause

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!

Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Combine conditions with AND in the Mysql ON clause

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录