CODEWITHSUNDEEP
sqlmysql
makeee
makeee

Reputation: 2805

MYSQL one to many JOIN

I have this query and I need to include another join on a table called "likes" where updates.id = likes.update_id. There will be 0 or more matches on this join. 

"SELECT * FROM users 
INNER JOIN updates ON users.remote_id=updates.owner_id 
ORDER BY updates.status_time DESC LIMIT 50"

This is probably fairly simple, but I haven't been able to find any examples for this kind of query.

The situation is basically that I'm displaying a list of items. I do a join on the users table to grab the user who created each item. I also need to do a join on the "likes" tables to display the 0+ people who liked each item.

EDIT: My Solution

Ok, here's the successful join and combining of duplicate results (due to the fact that there are multiple "likes" for each update) using GROUP_CONCAT.

"SELECT   users.*,updates.update_id,updates.content,updates.status_time,
GROUP_CONCAT(likes.liker SEPARATOR ',') AS liked_by 
FROM updates 
LEFT OUTER JOIN likes ON updates.update_id = likes.update_id 
JOIN users ON users.remote_id = updates.owner_id 
GROUP BY updates.update_id 
ORDER BY updates.status_time DESC 
LIMIT 200"

Upvotes: 0

Views: 6891

Answers (2)

perrohunter
perrohunter

Reputation: 3526

if you have a 1 to n relation the you should use a LEFT JOIN , i mean if there's only 1 user and many likes.user_id you should doit with the following way

"SELECT * FROM users LEFT JOIN updates ON (users.remote_id=updates.owner_id) ORDER BY updates.time DESC LIMIT 50"

this way you'd get all the updates from a certain user :)

cheers

Upvotes: 1

Kalium
Kalium

Reputation: 4682

SELECT * FROM users 
LEFT OUTER JOIN updates ON users.remote_id=updates.owner_id
INNER JOIN likes ON <join condition here>
ORDER BY updates.time DESC LIMIT 50

Will that work?

If not, can you clarify what exactly your question is? Does the query you have not work?

Upvotes: 2

Related Questions