I have sample data that looks like this (complete schema and data on SQL fiddle):
| users |
| user_id | email |
|---------|--------------------------|
| 1 | ashutosh8657@example.com |
| 2 | Kanchhi@example.com |
| 3 | modi@example.com |
| 4 | andy@example.com |
| 5 | maya@example.com |
| 6 | jetli@example.com |
| 7 | john@example.com |
| user_relations |
| user_relation_id | requestor_user_id | receiver_user_id | friend_status |
|------------------|-------------------|------------------|---------------|
| 1 | 2 | 4 | 1 |
| 2 | 2 | 6 | 1 |
| 3 | 2 | 7 | 1 |
| 4 | 5 | 2 | NULL |
| 5 | 5 | 7 | NULL |
| 6 | 7 | 2 | NULL |
| 7 | 7 | 4 | 1 |
| 8 | 7 | 5 | 1 |
| 9 | 7 | 6 | 1 |
| 10 | 4 | 2 | 1 |
| 11 | 4 | 3 | 1 |
| 12 | 4 | 5 | 1 |
| 13 | 4 | 6 | 1 |
| 14 | 4 | 7 | 1 |
If input is these two emails:
Kanchhi@example.com, john@example.com
Then my expected expected output is this (order does not matter):
andy@example.com
jetli@example.com
In the above example, the friends of user id 2 is user ids (4, 6, 7) and friend of user id 7 is user ids (2, 4, 5, 6). So the mutual friend of user id 2 and 7 is 4 and 6. I need email address of mutual user id.
Another example input is:
andy@example.com, john@example.com
Then expected output is this:
jetli@example.com
Kanchhi@example.com
maya@example.com`
In the above example, friend of user id 4 is (2, 6, 5, 3, 2) and friend of user id 7 is (6, 5, 2, 4). So mutual friend user id will be 2, 6, 5. I need email address of these user id in output.
Query 1- I tried but got wrong result:
SELECT u.email
FROM user_relations r
LEFT JOIN users u ON r.requestor_user_id = u.user_id
LEFT JOIN users z ON r.receiver_user_id = z.user_id
where u.email in ('Kanchhi@example.com','john@example.com') or
z.email in ('Kanchhi@example.com','john@example.com')
and r.friend_status = 1
group by u.email
having count(u.email ) > 1
| email |
|---------------------|
| andy@example.com |
| john@example.com |
| Kanchhi@example.com |
How to get this?