Left join on multiple tables causes Not unique table/alias

enfys

I have a database which contains three tables:

client_table
agent_table
stages_table

the client table has its own id field as well as a agent_id and stage_id field to create the join.

I need to display all records from the client_table as well as the relevant data from the other two tables. However, when I create the following select statement I get a "Not unique table/alias" error.

"SELECT client_table., agent_table., stages_table.* FROM client_table, agent_table, stages_table LEFT JOIN agent_table ON client_table.agent_id = agent_table.agent_id LEFT JOIN stages_table ON client_table.stage_id = stages_table.stage_id ORDER BY client_table.last_name ASC";

Help!!

bubblenut

Select each column out seperately and if any have the same name aliase them to a unique name with the as keyword. The problem is that you have two column names in two of those tables which are the same (prehapse unique_id in more than one table?)
HTH
Bubble

enfys

Thanks

I've now changed the select query to:

$client_query="SELECT client_table.id, client_table.agent_id, client_table.stage_id, client_table.f_name, client_table.l_name, client_table.address, client_table.city, client_table.postcode, client_table.phone, client_table.fax, client_table.mobile, client_table.email, client_table.web, client_table.notes, date_format(client_table.date_added, '%d/%m/%y') as fmt_date_added, date_format(client_table.date_modified, '%d/%m/%y') as fmt_date_modified, client_table.user_name, client_table.password FROM client_table AS cl_table LEFT JOIN client_table ON agent_table.id = client_table.agent_id LEFT JOIN client_table ON client_table.stage_id = stages_table.id ORDER BY client_table.l_name ASC";

But I'm still getting the same error.

any ideas?

mtimdog

It should read

....
left join agent_table on....

left join stages_table on ...

enfys

but won't that only show records from the client table if they have a corresponding entry in the agent and stages table? Whereas I need to show all records on the client table regardless of anything in the agent and stages tables.

mtimdog

http://www.hyperdictionary.com/dictionary/outer+join No , a left (outer) join is what you want.

For info on how to use it, look at the MySQL Manual