CODEWITHSUNDEEP
mysql
Al12
Al12

Reputation: 63

Query using left join

i have this query, 

$query = 'SELECT * FROM users where id ="'.$_SESSION['user_id'].'"';

My problem is that i don't know how to left join it with another table..using another where or "On" clause..

can you please help me with the syntax of this?

It should be something like SELECT $query FROM users,retailer ON $query.user_id=retailer.user_id ....

"SELECT users.first_name, retailer.date
FROM users AS users
LEFT JOIN retailer AS retailer ON users.user_id=retailer.user_id
WHERE users.user_id=" . $_SESSION['user_id']

Thanks!

Upvotes: 0

Views: 103

Answers (3)

Ivan Hušnjak
Ivan Hušnjak

Reputation: 3503

LEFT JOIN is pretty simple

For your example:

$query = "SELECT u.*, r.*
FROM users AS u
LEFT JOIN retailer AS r ON u.user_id=r.user_id
WHERE u.user_id=" . $_SESSION['user_id']

"AS" keyword creates alias so there is no need of making alias "users" of table "users".

Secondly, LEFT JOIN is useful if there is a chance that second table contains no matches to first one yet you still need that attribute to show - attributes of second table will have NULL values if no matches found.

I'm not really sure you need a LEFT JOIN in your query.

Upvotes: 1

Barmar
Barmar

Reputation: 780724

Another way to do it is:

$query = "SELECT u.*, r.*
FROM users u, retailer r
WHERE u.user_id=" . $_SESSION['user_id'] . "
AND u.user_id=r.user_id";

I've noticed that SO posters tend to use the explicit JOIN language, is it more standards-compliant or just a stylistic choice? I know it's necessary for LEFT OUTER JOIN, so is this just for consistency?

Upvotes: 0

Phil Cross
Phil Cross

Reputation: 9302

SELECT users.*, retailers.*  
FROM retailer 
LEFT JOIN users 
ON retailer.user_id=users.user_id

Upvotes: 0

Related Questions