Using Joins effectively.

[paddycallaghan]

Hi all, hopefully this will be simple for the likes of you all out there but it has been driving me nuts for a while now.

Basically, i am trying to display data received when i query using Joins. Basically i am bringing back client info, there is only one of these and I am then Joining two other tables on the client_id.

I c an bring back data fine, even quite impressed, the only problem is how do i get to the second, third etc returned rows of the of the other tables.

I mean, there is only one client, but the history and contact tables have multiple rows with the same id.

Any ideas.

Here is my current query.

$query = "SELECT * FROM db.client_info ci, db.client_contact cc, db.client_employee_history ch WHERE ci.client_id=cc.client_id AND ci.client_id=ch.client_id AND ci.client_id = $id";

I am then storing this in an array (this may not be the best way either so any feedback there would be helpful.

$result = mysql_query($query);
$query_array = mysql_fetch_array($result);

So i can view the details and get them displaying fine for the first array, but on the next one,if indeed it exists as there is only one client i am not sure how to do.

Thanks.
Paddy

[leatherback]

Hi,

You should really use the JOIN syntax (at [url]http://www.mysql.org)[/url]. I think a left join,

Yours,

J.

e.g.
select * from table1 left join table2 on table1.id = table2.id left join table 3 on table1.id = table3.id