how can i join twice two mysql tables...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
limonada
Forum Newbie
Posts: 11
Joined: Sat Oct 08, 2005 7:23 am

how can i join twice two mysql tables...

Post by limonada »

hello ! i need to join twice two mysql tables and i don't know how.What i want to do looks something like this:

SELECT *
FROM emails
left JOIN personen ON emails.id_person = personen.id
left JOIN themen ON emails.id_thema = themen.id
left join login on emails.id_berater=login.id
left join login on emails.id_supervisor=login.id

except that i cannot join with login.id twice.What would the solution be?
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

SELECT *
FROM emails
left JOIN personen ON emails.id_person = personen.id
left JOIN themen ON emails.id_thema = themen.id
left join login on emails.id_berater=login.id
OR emails.id_supervisor=login.id

At a guess.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you "can't" join them because of a table referencing ambiguity:

Code: Select all

SELECT *
FROM emails
left JOIN personen ON emails.id_person = personen.id
left JOIN themen ON emails.id_thema = themen.id
left join login a on emails.id_berater=a.id
left join login b on emails.id_supervisor=b.id
limonada
Forum Newbie
Posts: 11
Joined: Sat Oct 08, 2005 7:23 am

Post by limonada »

here is the solution i found:

Code: Select all

CREATE temporary TABLE `login2`(....)
INSERT INTO login2 SELECT ....FROM login
SELECT  * FROM emails
	LEFT JOIN personen ON emails.id_person = personen.id
	LEFT JOIN themen ON emails.id_thema = themen.id
	LEFT JOIN login ON emails.id_berater = login.id
	LEFT JOIN login2 ON emails.id_supervisor = login2.id
Post Reply