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

Question

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 ?

1000111 · Accepted Answer · 2016-07-10T05:39:33.697

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;

I've one more doubt. Can you please tell me how to fetch this values on my php page . — chirag unadkat, Jul 10 '16 at 06:46
Please look at this [**post**](http://stackoverflow.com/questions/17902483/show-values-from-a-mysql-database-table-inside-a-html-table-in-a-page) and [**more**](http://www.w3schools.com/php/php_mysql_select.asp). — 1000111, Jul 10 '16 at 06:48

score 0 · Answer 2 · answered Jul 10 '16 at 05:35

You should not join a table multiple times in a single statement. Erase the last join part of your code, hope it will work fine.

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 ;    // If you want to add another condition, just write AND keyword and specify in this line.

join club on members.coordi_club_id = club.id //   You need not use this line

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

2 Answers2