Need Help with a Join

[spudz]

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).

im_galleries [
g_id (PRIMARY KEY)
g_title
g_description
g_slug
g_thumbnail	 
g_created_on]

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]

[spudz]

Okay, so I did some messing around in PHP MyAdmin, here's what I came up with...

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?

[r937]

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

[spudz]

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.

[r937]

well, for one thing, it only goes after the images table once instead of twice

[spudz]

I've implemented a groups system for grouping galleries,

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?

[r937]

with joins, similar to post #3

give it a try

[spudz]

I had a go myself earlier, I'm getting confused with the joins and where to put them

[spudz]

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

[r937]

okay, i'll walk you through it

can you write a query to return the names of all galleries in each group

[spudz]

okay, i'll walk you through it

can you write a query to return the names of all galleries in each group

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.

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

[r937]

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

[spudz]

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...

[r937]

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

[spudz]

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...?

[r937]

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?

[spudz]

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

[r937]

okay, that little wrinkle i wouldn't expect you to be able to come up with on your own, so here, try this --

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

[spudz]

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?

[r937]

sorry to hear about your news

try this --

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

[spudz]

sorry to hear about your news

try this --

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?

[r937]

a simple oversight, eh

they were just missing from the SELECT clause --

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

[spudz]

That worked perfectly, thanks for all your help.