Board index   FAQ   Search  
Register  Login
Board index php forum :: PHP and MySQL Security PHP & MySQL Security

how to join more than 2 tables ?

Security issues related to php and mysql usage. How to make your code secure? Security measures and configurations? It's all in here!

Moderators: macek, egami, gesf

how to join more than 2 tables ?

Postby Miss_Marple » Tue Aug 31, 2004 9:37 am

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
Miss_Marple
New php-forum User
New php-forum User
 
Posts: 32
Joined: Tue Jul 30, 2002 11:27 pm

Postby Alex » Sun Sep 05, 2004 4:18 pm

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...
Alex
New php-forum User
New php-forum User
 
Posts: 180
Joined: Fri Mar 07, 2003 4:59 pm


Return to PHP & MySQL Security

Who is online

Users browsing this forum: No registered users and 2 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.