SitePoint Sponsor

User Tag List

Results 1 to 23 of 23
  1. #1
    SitePoint Zealot
    Join Date
    May 2005
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need Help with a Join

    Hi, I was wondering if someone could help me with a join, I have two tables, one for galleries, the other for images.

    I don't know if its possible but I'd like to get all the gallery information but also how many images are in a gallery and also the size of the gallery (which is saved in bytes).

    Code:
    im_galleries [
    g_id (PRIMARY KEY)
    g_title
    g_description
    g_slug
    g_thumbnail	 
    g_created_on]
    Code:
    im_images [
    i_id (PRIMARY KEY)
    gallery_id (FOREIGN KEY)
    i_type
    i_size
    i_filename
    i_title
    i_caption
    i_width
    i_height
    i_created_on]

  2. #2
    SitePoint Zealot
    Join Date
    May 2005
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, so I did some messing around in PHP MyAdmin, here's what I came up with...
    Code MySQL:
    SELECT g.*, 
    	(
    		SELECT count( * ) 
    		FROM im_images i 
    		WHERE g.g_id = i.gallery_id
    	) AS imageCount, 
    	(
    		SELECT SUM(i.i_size) 
    		FROM im_images i 
    		WHERE g.g_id = i.gallery_id
    	) AS totalSize 
    FROM im_galleries g
    LIMIT 0 , 30

    It works but is there a better way?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT g.g_id
         , g.g_title
         , g.g_description
         , g.g_slug
         , g.g_thumbnail	 
         , g.g_created_on
         , COALESCE(a.imageCount,0) AS imageCount
         , COALESCE(a.totalSize,0) AS totalSize
      FROM im_galleries AS g
    LEFT OUTER
      JOIN ( SELECT gallery_id
                  , COUNT(*) AS imageCount
                  , SUM(i_size) AS totalSize               
               FROM im_images 
             GROUP BY gallery_id ) AS a
        ON a.gallery_id = g.g_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    May 2005
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT g.g_id
         , g.g_title
         , g.g_description
         , g.g_slug
         , g.g_thumbnail	 
         , g.g_created_on
         , COALESCE(a.imageCount,0) AS imageCount
         , COALESCE(a.totalSize,0) AS totalSize
      FROM im_galleries AS g
    LEFT OUTER
      JOIN ( SELECT gallery_id
                  , COUNT(*) AS imageCount
                  , SUM(i_size) AS totalSize               
               FROM im_images 
             GROUP BY gallery_id ) AS a
        ON a.gallery_id = g.g_id
    Thanks for replying, I'd be very interested to learn why your method is better, I'm still trying to get the basics of SQL down, thanks for helping me out.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, for one thing, it only goes after the images table once instead of twice

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    May 2005
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've implemented a groups system for grouping galleries,

    Code SQL:
    CREATE TABLE `im_groups` (
      `group_id` INT(11) NOT NULL AUTO_INCREMENT,
      `group_title` VARCHAR(255) DEFAULT NULL,
      `group_description` tinytext,
      `group_order` INT(11) DEFAULT NULL,
      `group_slug` VARCHAR(40) DEFAULT NULL,
      PRIMARY KEY  (`group_id`),
      UNIQUE KEY `group_slug` (`group_slug`)
    );

    Any ideas how would to get the total amount of galleries, total size, total amount of images per group?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    with joins, similar to post #3

    give it a try

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    May 2005
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had a go myself earlier, I'm getting confused with the joins and where to put them

  9. #9
    SitePoint Zealot
    Join Date
    May 2005
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, let me expand, I'm not so good with MySQL, I was getting confused with whether or not I should create another LEFT OUTER JOIN on the galleries table to the groups table or if I should make a sub select or what? I kept getting frustrated by silly syntax errors, in the end I just gave up and posted it here

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, i'll walk you through it

    can you write a query to return the names of all galleries in each group
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    May 2005
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    okay, i'll walk you through it

    can you write a query to return the names of all galleries in each group
    Code SQL:
    SELECT groups.*, galleries.g_title 
    FROM im_groups groups, im_galleries galleries 
    WHERE groups.group_id = galleries.group_id

    Something like that is the first thing that comes to mind, although I suppose JOINS are more the way to go.

    Code SQL:
    SELECT groups.*, galleries.g_title
    FROM im_groups groups 
    LEFT JOIN im_galleries galleries
    ON groups.group_id = galleries.group_id

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, you're on the right track

    try to stop using the dreaded, evil "select star"

    in this example, just return the name of the group

    LEFT JOIN is correct, the other query with the commas is actually an inner join

    okay, now modify the query to return the count of the galleries in each group
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Zealot
    Join Date
    May 2005
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SELECT groups.*, galleries.g_title
    FROM im_groups groups 
    LEFT JOIN ( SELECT COUNT(*) AS galleryCount FROM im_galleries ) 
    AS galleries
    ON groups.group_id = galleries.group_id

    Something like that...

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, three problems with that

    first, you're still using the dreaded, evil "select star"

    second, you're not returning the count

    third, the join won't work because the subquery doesn't produce the group_id to join on

    try not to use a subquery this time, just a simple left outer join to im_galleries, and put the GROUP BY into the main query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Zealot
    Join Date
    May 2005
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SELECT groups.group_id
        , groups.group_slug
        , groups.group_title
        , galleries.g_title
        , COUNT(galleries.g_id) AS galleryCount
    FROM im_groups groups
    LEFT OUTER JOIN im_galleries galleries
    ON groups.group_id = galleries.group_id
    GROUP BY groups.group_id

    That worked, anything I'm doing wrong here...?

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that's excellent, except for one tiny little detail

    if a group has multiple galleries, then presumably they might all have different titles, yes?

    so putting galleries.g_title into the SELECT clause will return a value from just one of them, but there's no way to predict in advance which one it will be

    what were you hoping to achieve by that?

    otherwise, that count query is fine

    want to move onto the next step?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Zealot
    Join Date
    May 2005
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, the next step is getting the imageSize and imageAmount for each gallery, is that another JOIN?

    I suppose it would be nice to call out all the gallery titles and ids tied to each group, that way I could link to each gallery directly from the overall groups listing. e.g.

    Group Title
    ==========================
    Galleries: 3 (gallery1, gallery 2, gallery3)
    Total Size: 3.45MB
    Total Images: 115

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, that little wrinkle i wouldn't expect you to be able to come up with on your own, so here, try this --
    Code:
    SELECT gr.group_id
         , gr.group_slug
         , gr.group_title
         , COUNT(ga.g_id) AS galleryCount
         , GROUP_CONCAT(
               CONCAT(ga.g_id,':',ga.g_title)
                       ) AS galleries
      FROM im_groups AS gr
    LEFT OUTER 
      JOIN im_galleries AS ga
        ON ga.group_id = gr.group_id
    GROUP 
        BY gr.group_id
    let me know if you have any problem understanding what it's doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Zealot
    Join Date
    May 2005
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, sorry for the late reply, we had a death in the family, very busy last few days.

    Anyway! That's was a great help the last day, I wonder though, is it possible to also get the total size and total amount of Images from table `im_images` in this single query?

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry to hear about your news

    try this --
    Code:
    SELECT gr.group_id
         , gr.group_slug
         , gr.group_title
         , ggi.galleryCount
         , ggi.galleries
      FROM im_groups AS gr
    LEFT OUTER 
      JOIN ( SELECT group_id
                  , COUNT(*)   AS galleryCount  
                  , SUM(imageCount) AS imageCount        
                  , SUM(totalSize) AS totalSize        
                  , GROUP_CONCAT(                     
                        CONCAT(g_id,':',g_title)
                                ) AS galleries        
               FROM ( SELECT ga.g_id  
                           , ga.title
                           , ga.group_id                   
                           , COUNT(im.i_id) AS imageCount        
                           , SUM(im.i_size) AS totalSize      
                        FROM im_galleries AS ga   
                      LEFT OUTER
                        JOIN im_images AS im
                          ON im.gallery_id = ga.g_id
                      GROUP 
                          BY ga.g_id  ) AS gi
             GROUP
                BY group_id  ) AS ggi
        ON ggi.group_id = gr.group_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Zealot
    Join Date
    May 2005
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    sorry to hear about your news

    try this --
    Code:
    SELECT gr.group_id
         , gr.group_slug
         , gr.group_title
         , ggi.galleryCount
         , ggi.galleries
      FROM im_groups AS gr
    LEFT OUTER 
      JOIN ( SELECT group_id
                  , COUNT(*)   AS galleryCount  
                  , SUM(imageCount) AS imageCount        
                  , SUM(totalSize) AS totalSize        
                  , GROUP_CONCAT(                     
                        CONCAT(g_id,':',g_title)
                                ) AS galleries        
               FROM ( SELECT ga.g_id  
                           , ga.title
                           , ga.group_id                   
                           , COUNT(im.i_id) AS imageCount        
                           , SUM(im.i_size) AS totalSize      
                        FROM im_galleries AS ga   
                      LEFT OUTER
                        JOIN im_images AS im
                          ON im.gallery_id = ga.g_id
                      GROUP 
                          BY ga.g_id  ) AS gi
             GROUP
                BY group_id  ) AS ggi
        ON ggi.group_id = gr.group_id
    Hi, sorry for the late reply, this worked but it didnt give back the imageSize or the totalSize columns. I tried messing with it but I dont know enough of this stuff, can you spot anything on second look as to why its not showing the other columns?

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    a simple oversight, eh

    they were just missing from the SELECT clause --
    Code:
    SELECT gr.group_id
         , gr.group_slug
         , gr.group_title
         , ggi.galleryCount
         , ggi.imageCount
         , ggi.totalSize
         , ggi.galleries
      FROM im_groups AS gr
    LEFT OUTER 
      JOIN ( SELECT group_id
                  , COUNT(*)   AS galleryCount  
                  , SUM(imageCount) AS imageCount        
                  , SUM(totalSize) AS totalSize        
                  , GROUP_CONCAT(                     
                        CONCAT(g_id,':',g_title)
                                ) AS galleries        
               FROM ( SELECT ga.g_id  
                           , ga.title
                           , ga.group_id                   
                           , COUNT(im.i_id) AS imageCount        
                           , SUM(im.i_size) AS totalSize      
                        FROM im_galleries AS ga   
                      LEFT OUTER
                        JOIN im_images AS im
                          ON im.gallery_id = ga.g_id
                      GROUP 
                          BY ga.g_id  ) AS gi
             GROUP
                BY group_id  ) AS ggi
        ON ggi.group_id = gr.group_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Zealot
    Join Date
    May 2005
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That worked perfectly, thanks for all your help.


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
  •