CODEWITHSUNDEEP
phpmysqlsql
Simon Tong
Simon Tong

Reputation: 410

Find missing rows from table through a many to many relation

I have 3 tables

user table:

+---------+---------------------+
| user_id | username            |
+---------+---------------------+
|    1136 | 007                 |
|     978 | 11zep11the11hyerian |
|    1049 | 123456789           |
+---------+---------------------+

setting table:

+------------+----------------+
| setting_id | identifier     |
+------------+----------------+
|          1 | email-on-like  |
|          2 | email-on-reply |
|          3 | share-on-vote  |
+------------+----------------+

and user_setting table:

+-----------------+---------+------------+-------+
| user_setting_id | user_id | setting_id | value |
+-----------------+---------+------------+-------+
|               6 |      49 |          2 | 1     |
|               8 |      53 |          2 | 1     |
|               9 |      54 |          1 | 1     |
+-----------------+---------+------------+-------+

The setting table represents the available settings a user can have against their account. The user_setting table is where those settings are saved. Sometimes a new setting is added to the setting table and basically I want to create a query which will reflect the missing rows in the user_setting table so I can insert them accordingly.

Upvotes: 1

Views: 512

Answers (2)

Rob Baillie
Rob Baillie

Reputation: 3460

If you where looking for the missing settings for a single user you have the following two options:

SELECT s.setting_id
FROM setting AS s
WHERE s.setting_id NOT IN (SELECT us.setting_id FROM user_setting AS us WHERE us.user_id = ? )

or on Oracle:

SELECT s.setting_id FROM setting s
MINUS
SELECT us.setting_id FROM user_setting us WHERE us.user_id = ?

So, you could loop over the records in your user table, and then do this for each record.

Alternatively you can expand these two methods to include the user table by learning to love the cartesian join. I have to admit that I'm not mysql expert (I'm an Oracle boy), so I'll try my best for you.

A cartesian join is a join between two tables (or result sets) where every record in one should join to every record in the other. That is, there does not need to be an explicit relationship between the tables.

In your case, a cartesian join between user and setting would give you an exhaustive list of all the possible combinations between user and setting regardless of if a setting exists in your join table.

The join to give you all possible setting and user combinations would be (on mysql):

SELECT u.user_id, s.setting_id FROM user AS u CROSS JOIN setting AS s

The two above SQL staments then change to:

SELECT u.user_id, s.setting_id
FROM user AS u
CROSS JOIN setting AS s
WHERE (u.user_id, s.setting_id ) NOT IN (SELECT us.user_id, us.setting_id
                                         FROM user_setting AS us)

Or (as an alternative on Oracle)

SELECT u.user_id, s.setting_id
FROM user u, setting s
MINUS
SELECT us.user_id, us.setting_id
FROM user_setting us

Forgive me if my mysql syntax isn't spot on...

Upvotes: 4

wvdz
wvdz

Reputation: 16641

Thinking a little bit beyond your actual question, I'd say this is what you want:

CREATE TRIGGER update_user_settings
AFTER INSERT ON setting
FOR EACH ROW
BEGIN
  INSERT INTO user_settting (setting_id,user_id)
  SELECT new.setting_id, u.user_id FROM user u;
END;

Upvotes: -1

Related Questions