how to join more than 2 tables ?

[Miss_Marple]

Hi everybody,

I want to get data from 3 tables. A silly example:

I want ALL rows from table 'people'.
If there are rows with additional infos in tables 'address' and 'cars', I want some data from those tables, too.

If I write:

Code: Select all

SELECT * FROM people,address,cars WHERE
people.address_id = address.address_id AND
people.cars_id = cars.cars_id

...I only get those rows of people where data exists in all 3 tables. A small result. When I leave the WHERE clause, the result is multiple number of rows in people.

With 2 tables I could do a JOIN like:

Code: Select all

SELECT * FROM address RIGHT JOIN people ON
people.address_id = address.address_id

How must I do that with more tables?

Thanks

Alex

[Alex]

I was not sure about the synthax of join, so i went on to mysql.com and read some comments on join, found one thing you can use; posted by Alan G-B was the following:

I've seen requests for joining more than two
tables.
This goes a little further in that it joins three tables,
one of which is aliased three times.

The advert table contains adverts in which there
are
three region fields - region1,region2,region3; and a
category field.

Both the region and category tables have a key
id
field to join to the adverts table and a description
field.

The SQL is as follows...


SELECT adverts.*, categories.description AS
cat_desc, regions_1.description AS
region1_desc,regions_2.description AS
region2_desc,regions_3.description AS region3_desc
FROM adverts
RIGHT JOIN regions AS regions_3 ON
regions_3.regionID=adverts.region3
RIGHT JOIN regions AS regions_2 ON
regions_2.regionID=adverts.region2
RIGHT JOIN regions AS regions_1 ON
regions_1.regionID=adverts.region1
INNER JOIN categories ON
categories.categoryID=adverts.categoryID

This results in records showing all adverts with
the relevant descriptive text for each region and the
category.

now this uses join to join 3 regions of a table, I would think that similarly you can join 3 tables, and if that is so, i would try this:

Code: Select all

Select * from people
RIGHT JOIN address ON
people.address_id = adress.address_id
RIGHT JOIN cars ON
people.cars_id = cars.cars_id


Again I am not sure whether my answer is correct, i would have to experiment a little and actually create the tables and try to join them before i can myself find out whether i am correct or not, but i hope that that is a pretty good guess from a working example above...