multiple table & id query

[lazeelaker]

I have a multi-table query that I want to retrieve certain results for. I need to retrieve all of this information for specific ID's. My query is valid in that results are returned, but it is flawed as any and all matches are returned.
Here is an example:

SELECT DISTINCT t1.first, t1.last, t1.street, t2.category, t3.specialty FROM t1, t2, t3 WHERE t1.ID=1 OR t1.ID=2 OR t1.ID=4 AND t1.catID=t2.catID AND t1.specialtyID=t3.specialtyID

I need these values (t1.first, t1.last, t1.street, t2.category, t3.specialty) for each t1.ID listed. Is it possible to do this in 1 query?

[Roger_Ramjet]

2 things wrong with that query:

$sql = " SELECT DISTINCT t1.first, t1.last, t1.street, t2.category, t3.specialty FROM t1 INNER JOIN t2 ON (t1.catID=t2.catID) INNER JOIN  t3 ON (t1.specialtyID=t3.specialtyID)  WHERE t1.ID=1 OR t1.ID=2 OR t1.ID=4";

[jansky]

you should use left join

select distinct yourfield
from yourtable left join on (yourid)
where
id=id

something like that

[Roger_Ramjet]

Why the left join? Nothing in the post or original query indicates that data exists in t1 without related records in t2 and t3, or that he wants it if such data should exist.. Also, in a 3-table query it can pay in optimising processing not to use the USING clause form.

[jansky]

ooppsss.. sorry did not post an explanation why should use left join thanks for covering up
roger amjet