Page 1 of 1

using joins

Posted: Sun Aug 07, 2005 11:40 am
by taldos
Hi all,

I have only recently begun looking at joins as an optimization method for some of my code. I would like to pull data from two tables into one result set. However, instead of listing each column that I would like to pull from both tables I wonder if there is a way to get all columns from one table and a select few from another.

ie

"SELECT table1.*, table2.id, table2.f_name, table2.l_name FROM table1, table2 WHERE table1.root_id=".$cid." AND table1.poster_id=table2.id"

essentially I am searching table 1 for any threads posted in a particular category and I would like to pull the username and id from the second table only.

Posted: Sun Aug 07, 2005 11:48 am
by Sander
I think this is what you want:

Code: Select all

SELECT t1.*, t2.id, t2.f_name, t2.l_name 
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON t1.poster_id = t2.user_id
WHERE table1.root_id = " . $cid

Posted: Sun Aug 07, 2005 11:56 am
by nielsene
I would probably remove the "LEFT" from the query. This doesn't look like a place where an "outer join" would be desired.

Posted: Sun Aug 07, 2005 12:07 pm
by taldos
Muchas gracias. Now I can finally go through all my pages of code and optimize. :-)

Cheers.