[RESOLVED] MySQL JOIN: Joining one field, to many fields?

joshd963

Hi guys, you recently helped me regarding JOINs, thanks for that, i really appreciate it, i'm starting to make some good progress now.

I have however come across a fresh problem, and this one has completely blown me.

So i have my MySQL Query -
[INDENT]"SELECT * FROM liners LEFT JOIN port ON liners.ID=port.LINERS LEFT JOIN people ON liners.LINE_MANAGER=people.ID ORDER BY PORT ASC"[/INDENT]

That's great... it pulls up the liner, all the info about the liner, and all the info about the line manager.
However, in the "liners" table, i also have a few fields for other personnel. I'm finding it difficult to perform a JOIN on their fields, since i have already made a join to the people.ID field on liners.LINE_MANAGER.

and people structure like this :
ID | NAME | EXTENSION | EMAIL

so for example if i had a line manager with id TP...
the join would grab him from the people table on liners.LINE_MANAGER=people.ID

but since i have already made that join, i'm finding myself unable to JOIN liners.IMP_CONTACT1=people.ID

I was wondering if you guys have any ideas?

I tried aliasing the table, but i either did it wrong, or it didn't work. I think part of the problem lies in that it occupies the same variable name?
ie... since all the personnel are on the same table, the field for their name is always "NAME", so when i display it with PHP... its always

 echo $row['NAME'];

i figured a possible solution might be to copy the people table 5 times. one for linemananger, one for impcontact1, impcontact2, expcontact1, expcontact2....

however that would be duplicating data... and that's naughty. Plus it means i'd have to update the database five times everytime i make one change, which defeats the point of having it stored on a database

so i'm pretty stumped on this.. wondering if you guys have any neat functions or ideas up your sleeves? Brownie points your way..

Thanks in advance!🙂

johanafm

The alias always goes after the identifier, wether it's a field or table identifier

SELECT ... FROM liners LEFT JOIN people AS managers ON liners.line_manger = managers.id LEFT JOIN people AS contact1 ON liners... = contact1.id

joshd963

cheers geeza... your post seem to twig something in my brain and after quite a bit of tweaking i got it working.

what i noticed was that if i did a var_dump on my array,
the tables that had been aliased... their values were now numbers instead of 'name' 'id' etc.

Works a charm now.

Many thanks

bradgrafelman

Don't forget to mark this thread resolved (if it is) using the link on the Thread Tools menu above.

johanafm

joshd963;10976376 wrote:
the tables that had been aliased... their values were now numbers instead of 'name' 'id' etc.

Ahh, that will probably depend on what you use to communicate with the DB. The reason is that you are now retrieving many fields called 'name', many fields called 'id' etc. But you cannot have array('id' => ..., 'id' => ...).

You could however alias your fields as well

SELECT a.field AS a_field, b.field AS b_field FROM t AS a INNER JOIN t as b...