Spun a Spun a - 5 months ago 10
MySQL Question

I can't join 3 tables with one being joined twice for its columns

I have three tables, users table, family tables and family_relation table. Users table has all members, family table has two columns relation_id and relation_name. Family_relation table has 3 columns, member_id, second_member_id and relation_id.

In the family_relation table, I have these two rows, 1, 3, 1 and 1, 2 ,2 which should translated to member_id -> 1 second_member_id -> 3 and their relation is married.

The next row should translated to member 1 and member 2 are related by being siblings. When I join these three tables together, I get to have the 'member_id' translates to name from the users table but I cannot seem to get the 'second_member_id' to translate to a name from the users table. The family table translated well as well.

Also, if the structure of the tables and how they are related it bad, please inform me on how to have an efficient alternative.

Here is my code

SELECT family_relation.*, users.firstName, '+
' family.relation FROM family_relation '+
' JOIN users ON users.id = family_relation.member_id'+
' JOIN family on family.id = family_relation.relation_id '+
' WHERE family_relation.member_id = ?'+
' UNION SELECT family_relation.*, users.firstName,'+
' family.relation FROM family_relation'+
' JOIN users ON users.id = family_relation.member_id'+
' JOIN family on family.id = family_relation.relation_id'+
' WHERE family_relation.second_member_id = ?'


output:

[ RowDataPacket {
member_id: 11,
second_member_id: 40,
relation_id: 2,
firstName: 'Thomas',
relation: 'Child' },
RowDataPacket {
member_id: 11,
second_member_id: 31,
relation_id: 2,
firstName: 'Thomas',
relation: 'Child' },
RowDataPacket {
member_id: 11,
second_member_id: 42,
relation_id: 1,
firstName: 'Thomas',
relation: 'Spouse' } ]


I'm using union here because when user passes an id, it could be the second_member_id the one that equals to the passed id. The output is supposed to be something like, user passes an id, loop through table family_relation then output should be Thomas relation to Diana (Married/Child/Brother etc.) or Diana related to Thomas (Married/Child/Brother etc.).

Answer

That's because you need to join to the user table twice, once by member_id and once by second_member_id .

I don't understand what you code is doing, so I'll take just the first part of it :

SELECT family_relation.*, users.firstName,sec_u.first_name '+
    ' family.relation FROM family_relation '+
    ' JOIN users ON users.id = family_relation.member_id'+
    ' JOIN users sec_u ON sec_u.id = family_relation.second_member_id'+
    ' JOIN family on family.id = family_relation.relation_id '+
    ' WHERE family_relation.member_id = ?'+
Comments