CODEWITHSUNDEEP
sqlmysql
Bug Magnet
Bug Magnet

Reputation: 2668

Left Join with multiple tables question

I have the following tables in MySQL:

users profile rates

In the users table, I have the following record:

id: 1
name: John
status: active

In profile, I have the following:

id: 5
bio: blah blah blah
user_id: 1

The table rates is empty, but contains the following fields:

id, rate, and user_id

I am trying to use SQL to query these tables and display information about the user, their user profile (if they have one), and their rates (if one exists). The SQL I am using is as follows:

SELECT user.name, profile.bio, rate.rate 
FROM user 
LEFT JOIN (profile, rate) ON (user.id = profile.user_id AND user.id = rate.user_id) 
WHERE status = 'active';

The problem here is that the above sql returns user.name data, but the profile.bio is null even though there is a matching user_id record in the profile table. Seems that MySQL will not display the bio data if there is not a matching record for that user in the rates table.

If I want the SQL to display the user's name as well as their bio and hourly rate (if either one exists), how would I go about doing so? I'm a bit confused, as I thought the above SQL should work.

Thanks!

Upvotes: 2

Views: 305

Answers (3)

lijie
lijie

Reputation: 4871

Your original code was performing a left join on the user table and the table produced by the cross join of profile and rate.

The cross join of two tables is the cartesian product of the two tables. Specifically, it does not produce rows which only come from one table. Hence, when this cross joined table is left joined to user and with that where clause, you don't see the desired result.

To achieve your desired result, you need to left join multiple times.

Upvotes: 0

Jeremy
Jeremy

Reputation: 1025

I'm not familiar with doing multiple joins in one clause, so I would tr to split up the joins, like so:

SELECT user.name, profile.bio, rate.rate
FROM user
LEFT JOIN profile ON user.id = profile.user_id
LEFT JOIN rate ON user.id = rate.user_id 
WHERE status = 'active';

Upvotes: 2

Chuck Callebs
Chuck Callebs

Reputation: 16441

SELECT user.name, profile.bio, rate.rate
FROM user
LEFT JOIN profile ON user.id = profile.user_id
LEFT JOIN rate ON user.id = rate.user_id
WHERE status = 'active'

You were trying to combine the LEFT JOIN logic and it was only joining if both of your conditions were true (user.id = profile.user_id AND user.id = rate.user_id).

Upvotes: 2

Related Questions