shammon shammon - 3 months ago 9
MySQL Question

mysql join : get all rows that matches the rows that matches notification type is zero

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

Answer

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)