Easier way to do this

[RelaxoRy]

I have 3 tables with a id column in each matching

1. contact
2. personalinfo
3. photos

i want to query the personalinfo database :

SELECT *
FROM `talent_personalinfo`
WHERE `gender` = 'Female'
AND `nationality` = 'Australia'
AND `race` = 'Australian/European'

this is great. but I also want to get the information from the other
tables where the id from the returned values matches. isnt there some
fancy join that can do it for me? if I add the other tables into the
WHERE line, it just returns all the rows for as how many results i
have x 2

at the moment i'd have to query the first database, getting the id in
an array, then query them individual where the id = $arr[x]; loop

any help appreciated
cheerio

[Randell D.]

RelaxoRy wrote:

I have 3 tables with a id column in each matching

1. contact
2. personalinfo
3. photos

i want to query the personalinfo database :

SELECT *
FROM `talent_personalinfo`
WHERE `gender` = 'Female'
AND `nationality` = 'Australia'
AND `race` = 'Australian/European'

this is great. but I also want to get the information from the other
tables where the id from the returned values matches. isnt there some
fancy join that can do it for me? if I add the other tables into the
WHERE line, it just returns all the rows for as how many results i
have x 2

at the moment i'd have to query the first database, getting the id in
an array, then query them individual where the id = $arr[x]; loop

any help appreciated
cheerio

I'm a newbie... but you could try something like:

SELECT * FROM personalinfo, contact, photos
WHERE gender='Female'
AND nationality='Australia'
AND race='Australian/European'
AND personalinfo.id=contact.id
AND contact.id=photos.id

NOTE: Because the 'id' columns are common in all tables, you will get an
error because the SELECT * will get id from the three different tables
and get its knicers in a twist. The solution for this is to
specifically name the columns you want... Thus, you could change the
first line in the SELECT above to something like

SELECT personalinfo.id as pid, personalinfo.firstname,
personalinfo.whatever, contact.id as cid, contact.tel, photos.id as fid,
photos.somethingelse
WHERE ....
Note the usage of 'as' in the SELECT - This allows you to refer to
personalinfo.id as pid and remove duplicity...

I hope that helps - and I hope my syntax is correct too...

Note, what steps have you put in place to ensure the ID numbers of each
record retain the relationship with each other? If you were to add two
records at the same time, its possible that the server could give you a
mis-match (thus, for example, the complete data for personalinfo.id=1
might continue in contact.id=2 and photos.id=2).

To resolve this, use a foreign key that is inserted in to all three
records, and common (use an md5 hash forexample).

I hope this helps,

randelld