Page 1 of 1

how can i join twice two mysql tables...

Posted: Tue Oct 25, 2005 6:43 am
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?

Posted: Tue Oct 25, 2005 7:47 am
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.

Posted: Tue Oct 25, 2005 8:33 am
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

Posted: Wed Oct 26, 2005 5:11 am
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