Complex SQL query, one to many relationship
- by Ethan
Hey SO,
I have a query such that I need to get
A specific dog
All comments relating to that dog
The user who posted each comment
All links to images of the dog
the user who posted each link
I've tried a several things, and can't figure out quite how to work it. Here's what I have (condensed so you don't have to wade through it all):
SELECT s.dog_id,
s.name,
c.comment,
c.date_added AS comment_date_added,
u.username AS comment_username,
u.user_id AS comment_user_id,
l.link AS link,
l.date_added AS link_date_added,
u2.username AS link_username,
u2.user_id AS link_user_id
FROM dogs AS d
LEFT JOIN comments AS c
ON c.dog_id = d.dog_id
LEFT JOIN users AS u
ON c.user_id = u.user_id
LEFT JOIN links AS l
ON l.dog_id = d.dog_id
LEFT JOIN users AS u2
ON l.user_id = u2.user_id
WHERE d.dog_id = '1'
It's sorta close to working, but it'll only return me the first comment, and the first link all as one big array with all the info i requested. The are multiple comments and links per dog, so I need it to give me all the comments and all the links. Ideally it'd return an object with dog_id, name, comments(an array of the comments), links(an array of the links) and then comments would have a bunch of comments, date_added, username, and user_id and links would have a bunch of links with link, date_added, username and user_id. It's got to work even if there are no links or comments.
I learned the basics of mySQL somewhat recently, but this is pretty far over my head. Any help would be wonderful. Thanks!
