mysql join：获取与匹配通知类型的行匹配的所有行为零

I have two tables in my database

1.mir_notifications (id(PK),title,message,notifcaton_type)

2.mir_users_notifications (id,user_id,notif_id(FK))

notif_id refers to id in the mir_notification table

I want to fecth all rows from mir_notification table that matches a user_id in mir_users_notifcations table and notification_type in mir_notifications table along with all rows that has notifcation_type is 0

(ie if notifcation type is zero it will fetch for all users)

Here is the query i am used

SELECT mir_notifications.* FROM mir_notifications LEFT JOIN mir_users_notification ON mir_notifications.id=mir_users_notification.notif_id WHERE mir_notifications.notfication_type IN (0,2) AND  mir_users_notification.user_id=2

Here i am passing notification type 0 and 1 and userid is 2 ,currently this user have no special notification but he have a common notification (ie notification_type is 0) so it should returs rows from mir_notification table that matches notification_type is zero but it will returns empty results

You can simply modify your WHERE clause. The query below will get all notifications of type 2 for user 2, along with all notifications of type 0.

SELECT mir_notifications.* FROM mir_notifications LEFT JOIN mir_users_notification ON mir_notifications.id=mir_users_notification.notif_id WHERE (mir_notifications.notfication_type = 2 AND mir_users_notification.user_id=2) OR (mir_notifications.notification_type = 0)