chirag unadkat chirag unadkat - 4 months ago 8
SQL Question

Join single column of single table to multiple column of another table

Here is what I have tried so far.But it showed an error.

#1066 - Not unique table/alias: 'club'

select members.*, role.role_name, department.dept_name, club.club_name from members
join role on members.role_id=role.id
join department on members.dept_id=department.id
join club on members.club_id=club.id
join club on members.coordi_club_id = club.id


How can I resolve it ?

Answer

Try this please:

SELECT
    members.*, role.role_name,
    department.dept_name,
    CL1.club_name
FROM
    members
JOIN role ON members.role_id = role.id
JOIN department ON members.dept_id = department.id
JOIN club CL1 ON members.club_id = CL1.id
JOIN club CL2 ON members.coordi_club_id = CL2.id;

Note: You needed to give an alias to club table while joining the second time. For the sake of clarity I've used two aliases of club table (CL1,CL2).

EDIT: Since you are joining club table with the same instance of members table the second join becomes redundant this way.

Instead you should use the following query:

    SELECT
        members.*, role.role_name,
        department.dept_name,
        CL1.club_name
    FROM
        members
    JOIN role ON members.role_id = role.id
    JOIN department ON members.dept_id = department.id
    JOIN club CL1 ON members.club_id = CL1.id AND members.coordi_club_id = CL1.id;