Knuspakeks Knuspakeks - 1 year ago 88
MySQL Question

Select from 2 Tables, 2 Foreign Keys with 1 exception value | MySQL

I am trying to create a sql-statement for following problem:
I got two tables:

**Table user**
user_id | user_name
1 | Carl
2 | Claudia
3 | Janet
4 | Ben

**Table matching**
matching_id | user_id_1_FK | user_id_2_FK
1 | 1 | 2
2 | 3 | 1
3 | 4 | 2

Now want to have a list of all matchings/pairs where user carl is beeing part of it

The result should be:

user_name | user_id
Claudia | 2
Janet | 3

Thx for help in advance.

Answer Source

The value-add to such a query is not very obvious to me, but after playing around a little, I came up with the following solution:

SELECT DISTINCT other_user_id, target.user_name
    select user_id_1_FK as leading_user_id, user_id_2_FK as other_user_id
    from matching

    union all

    select user_id_2_FK as leading_user_id, user_id_1_FK as other_user_id
    from matching
) as allmatching 
LEFT JOIN user as target on allmatching.other_user_id = target.user_id
RIGHT JOIN user as source on allmatching.leading_user_id = source.user_id
WHERE source.user_name = 'Carl';

The trick is that, apparently, your matching table is specifying a bi-directional property: it does not matter from which side it is being read. To factor that into the query, a UNION operation is being used to factor this property into the statement. Afterwards, it is just a matter of joining the user table twice to allow using "natural" terms instead of these surrogate IDs.