I have 4 tables
users
| id |Username|
| 1 | John |
| 2 | Mike |
| 3 | Alex |
user_contacts
| user_id |contact_id|
| 1 | 2 |
| 1 | 3 |
| 2 | 3 |
contact_groups
| id | Group name |
| 1 | Group 1 |
| 2 | Group 2 |
| 3 | Group 3 |
user_contact_groups
| user_id |contact_group_id|
| 1 | 1 |
| 1 | 2 |
| 3 | 2 |
what id like to do is to pull users which belongs to Contact Group 1 and 3 or a contact of user 1 (in table:user_contacts). Below is code, but it returns query is empty
SELECT DISTINCT a.* from users as a
WHERE EXISTS (SELECT * FROM user_contacts as b
WHERE b.user_id = 1) OR
(a.id IN (select c.user_id
FROM user_contact_groups as c
WHERE c.contact_group_id IN (1,3)));
This is how I would do it, and should be the most optimal:
SELECT DISTINCT u.*
FROM users u
LEFT OUTER JOIN user_contacts c ON u.id = c.contact_id
AND c.user_id = 1
LEFT OUTER JOIN user_contact_groups g ON u.user_id = g.user_id
AND g.contact_group_id IN (1,3)
WHERE c.id IS NOT NULL OR g.id IS NOT NULL
If you wanted to use EXISTS, you can of course turn this around, but the query may not be able to use indexes (you can remove the DISTICT for this query):
SELECT *
FROM users u
WHERE EXISTS
(
SELECT 1
FROM user_contacts c
WHERE c.contact_id = u.id
AND c.user_id = 1
)
OR EXISTS
(
SELECT 1
FROM user_contact_groups g
WHERE g.user_id = u.user_id
AND g.contact_group_id IN (1,3)
)
I suggest doing an EXPLAIN and see which one is better for your RDBMS.
Instead of doing subqueries you could do left joins
SELECT DISTINCT a.*
FROM users as a
LEFT JOIN user_contacts as b ON a.id = b.user_id AND a.id = 1
LEFT JOIN user_contact_groups as c on c.user_id = a.id AND c.contact_group_id IN (1,3)
WHERE b.user_id IS NOT NULL OR c.user_id IS NOT NULL
I don't have SQL in front of me to test this, but I think it'll get the right results
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With