SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Addict n0other's Avatar
    Join Date
    Feb 2005
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple JOIN problem

    I have tables: gallery_album, gallery_photo, node.

    On node, the important columns are: type (type must be "gallery"), nid (integer node id), uid - node owner id.

    On gallery_photo, the important colums are: nid (relates photo to node entry), album_id - relates photos to gallery_album entries.

    On gallery_album, the important colums are: id (must match album_id in gallery_photo), title, must not be empty (check for <> "" does the trick), owner_id

    For every node entry of type "gallery" there exists exactly one entry in gallery_photos - one to one relationship.

    For every gallery_album entry there exists any number of gallery_photo and node entries - one to many relationship.

    I know the user id, I need to return albums belonging to that user together with photo count in that album and newest photo from that album (specified by nid and node.created timestamp).

    If album has 0 entries, I still need it!

    The problem is that I need to LEFT JOIN (right?) TWO tables (gallery_photo, nodes) on ONE (gallery_album), how do I do that?

    Would be very thankful for any ideas..
    Last edited by n0other; Feb 11, 2007 at 02:56. Reason: clarified the goal description

  2. #2
    SitePoint Addict n0other's Avatar
    Join Date
    Feb 2005
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Solved after many unsuccessful tries with this one:

    Code:
    SELECT a.id, a.title, a.country,
      (SELECT COUNT(g.nid)
        FROM gallery_photo g, node n2
        WHERE n2.type = "gallery" AND n1.uid = n2.uid AND g.nid = n2.nid AND g.album_id = a.id) AS photos
    FROM (gallery_album a LEFT JOIN gallery_photo g2 ON g2.album_id = a.id) LEFT JOIN node n1 ON n1.nid = g2.nid AND n1.type = "gallery"
    WHERE a.title <> ""
    Seems to work, but does that make any sense? Any ways to optimize it? Thanks

  3. #3
    SitePoint Addict n0other's Avatar
    Join Date
    Feb 2005
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope, the subselect doesn't count correctly, always 0..

  4. #4
    SitePoint Addict n0other's Avatar
    Join Date
    Feb 2005
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SOLVED (with the help of kind people on freenode)

    Code:
    SELECT a.id, a.title, COUNT(g.id), MAX(n.created), n.nid FROM gallery_album a LEFT JOIN gallery_photo g ON g.album_id = a.id LEFT JOIN node n ON n.nid = g.nid WHERE a.title <> "" GROUP BY a.id

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the people on freenode (whatever that might be) should read this --
    12.10.3 GROUP BY and HAVING with Hidden Fields

    the value you get for n.nid is unpredictable and may not correspond to the same row that gives MAX(n.created)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot mudda's Avatar
    Join Date
    Jul 2005
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You must have that page bookmarked Rudy, heh.

  7. #7
    SitePoint Addict n0other's Avatar
    Join Date
    Feb 2005
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So what should I do my groupping on then? n.nid?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by n0other View Post
    So what should I do my groupping on then? n.nid?
    no, not necessarily

    the best thing would be simply to exclude n.nid from the SELECT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict n0other's Avatar
    Join Date
    Feb 2005
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ugh, but it's essential for the correct working of the application.. So in other words, there is no sure way to select n.nid with this query / table structure?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, because that's not how grouping works

    "For every gallery_album entry there exists any number of gallery_photo and node entries"

    okay, so for every a.id (which is your GROUP BY column), there are multiple g.ids and multiple n.nids

    but because you are grouping, you will get only one result row per a.id

    so COUNT() is okay, and MAX() is okay, but n.nid by itself is unpredictable, because it could be any one of the values out of all the different values for that a.id

    does that make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict n0other's Avatar
    Join Date
    Feb 2005
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see, it certainly does make sense. In my situation, n.nid should belong to the same row as MAX(n.created), so if MAX is okay, I figure there should be some way to relate the two, right?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try this --
    Code:
    SELECT a.id
         , a.title
         , ( select count(*)
               from gallery_photo  
              where album_id = a.id ) as photos
         , n.created
         , n.nid 
      FROM gallery_album a 
    LEFT 
      JOIN gallery_photo g 
        ON g.album_id = a.id  
    LEFT 
      JOIN node n 
        ON n.nid = g.nid 
       AND n.created =
           ( select max(created)
               from node  
              where nid = g.nid )
     WHERE a.title <> ""
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict n0other's Avatar
    Join Date
    Feb 2005
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, though that's not exactly what I need. It selects multiple images per album, I need to select only ONE image per album - the most recent.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try changing the LEFT JOINs to INNER JOINs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Addict n0other's Avatar
    Join Date
    Feb 2005
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope, same thing I think I'll just use my previous query for now, the photo is not the most recent, but at least it's just one per album. Thanks r937


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •