0

I have 3 tables

USERS
user_id

ALERTS
alert_id

USER_ALERTS
user_id
alert_id
show_alert

I need to find out which users are NOT linked to which alerts.
So for the following data:

USER

user_id
--------
1       
2

ALERTS

alert_id
--------
9       
8   
7

USER_ALERTS

user_id alert_id
------- --------
1             9   
2             9   
1             8   
2             8  
1             7

The query should return

user_id alert_id
------- --------
2             7

I can find alerts that are not linked to ANY user by joining 2 tables:

select a.alert_id, ua.alert_id, ua.user_id from alerts a 
left join 
user_alerts ua 
on    a.alert_id = ua.alert_id
where u.alert_id is null;

But I can't seem to return users and alerts that are not linked - I need to update the linking table with the missing rows.

I can join all 3 tables (pretty much returns the user_alerts rows):

SELECT u.user_id, ua.alert_id FROM user u 
LEFT OUTER JOIN user_alerts ua on u.user_id = ua.user_id
LEFT OUTER JOIN alerts a on a.alert_id = ua.alert_id;

I can try a Cartesian product approach - combine all users with all alerts

 SELECT u.user_id, a.alert_id from user u, alerts a

Get all current users with alerts

 SELECT ua.user_id, ua.alert_id from user_alerts ua

Now I need all the values in the 1st query that aren't in the second query, but not sure exactly how to get there from here.

Anybody have any ideas?

I'm using mySQL

gebuh
  • 797
  • 14
  • 40
  • i hope that this question will give you some ideas. http://stackoverflow.com/questions/13502068/get-the-opposite-results-from-a-select-query – user3720852 Jun 11 '14 at 00:59

1 Answers1

2

Your idea about cartesian product is right. It can be achieved by joining users and alerts without any conditions. Finding which pair is missing is just filtering with left join

select a.*
from (
  select user_id, alert_id
  from users
  join alerts) a
left join user_alerts b on a.user_id = b.user_id and a.alert_id = b.alert_id
where b.user_id is null;

demo: http://sqlfiddle.com/#!2/eb803/2

Fabricator
  • 12,722
  • 2
  • 27
  • 40