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!

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).

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';

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.