Advanced Join help

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
Zmodem
Forum Commoner
Posts: 84
Joined: Thu Apr 18, 2002 3:59 pm

Advanced Join help

Post by Zmodem »

Greetings.

Lets say I have a table called 'users'.
Users has fields: favcolor, favanimal and favcar

Lets also say, I have 3 additional tables: colors, animals and cars.

favcolor, favanimal and favcar fields contain only the unique ID's of the color/animal/car in their respective tables.

Lets say I want to dump the contents of the users table:

John, 2, 6, 8 could be one of the rows. However, I want the actual text displayed, not the unique ID which it corresponds to.

John, red, eagle, ford mustang

I could use a JOIN for one of the fields, but is there a way I can join multiple tables to 'users' so that I can lookup the rest of the info?

If I were using pgSQL I suppose I could use subselects, but I don't have that luxery as I'm using MySQL.

Your help is greatly apprecaited.

--cheers
User avatar
haagen
Forum Commoner
Posts: 79
Joined: Thu Jul 11, 2002 3:57 pm
Location: Sweden, Lund

Post by haagen »

Try

Code: Select all

SELECT u.name, c.name, a.name, f.name FROM users u LEFT JOIN colors c ON c.ID = u.color LEFT JOIN animals a ON a.ID = u.animal LEFT JOIN cars f ON f.ID = u.favcar WHERE u.ID = $youruser
I think this would work. You then have to change so you'll get the right type of join (left, right, inner left usw.) for your query. But the query above might be a pointer to how it works.
Zmodem
Forum Commoner
Posts: 84
Joined: Thu Apr 18, 2002 3:59 pm

Post by Zmodem »

A little modification was required, but that did work!

I didn't realize you could use multiple LEFT JOIN's.

Thanks a bunch
Post Reply