Complex SQL query, one to many relationship

Posted by Ethan on Stack Overflow See other posts from Stack Overflow or by Ethan
Published on 2010-04-13T21:11:22Z Indexed on 2010/04/13 21:13 UTC
Read the original article Hit count: 316

Filed under:
|
|

Hey SO,

I have a query such that I need to get

  1. A specific dog
  2. All comments relating to that dog
  3. The user who posted each comment
  4. All links to images of the dog
  5. 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!

© Stack Overflow or respective owner

Related posts about mysql

Related posts about query