My MySQL JOIN query only gets data from one table

SELECT `users`.*
FROM (`users`)
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4 

That's my query. I have data in 开发者_运维问答users, users_profiles, albums, and users_sites. Currently I'm getting the necessary data in four separate queries but I want to optimise with JOINs.

My problem is that the result set being returned only contains one row of data from the users table (where the id is 4). This is to be expected but I want other tables JOINed on the end too.

For example, users_profiles contains a row that I have confirmed contains user_id set to 4. But no rows from users_profiles is ever added to the query, all I see is what comes from users.

Any ideas? It's the same case for the other tables - I've confirmed they should be selected properly but I'm not sure what I'm doing wrong.

Thanks for your help!

Try:

SELECT `users`.*, `users_profiles`.*, `albums`.*, `users_sites`.*
FROM (`users`)
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4 

Your code only selecting the columns from users table.

Alternatively,

SELECT *
FROM (`users`)
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4 

Please beware that selecting * is not recommended as it is not optimized. Just select the column that you need.

SELECT `users`.*, `user_profiles`.*, `albums`.*, `user_sites`.*
FROM (`users`)
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4 

You only select the columns from your users table;

SELECT users.*

Change your SELECT statement to include the columns you want to retrieve, to include all columns from all tables (affected by your query);

SELECT * FROM `users`
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4 

To include everything from the users_profile and users tables;

SELECT `users`.*, `users_profiles`.* FROM `users`
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4