using joins

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
taldos
Forum Commoner
Posts: 39
Joined: Mon Aug 23, 2004 8:47 am
Location: Philadelphia

using joins

Post 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.
Sander
Forum Commoner
Posts: 38
Joined: Sat Aug 06, 2005 12:43 pm

Post 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
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
taldos
Forum Commoner
Posts: 39
Joined: Mon Aug 23, 2004 8:47 am
Location: Philadelphia

Post by taldos »

Muchas gracias. Now I can finally go through all my pages of code and optimize. :-)

Cheers.
Post Reply