SitePoint Sponsor |
|
User Tag List
Results 1 to 23 of 23
Thread: Need Help with a Join
-
Nov 4, 2008, 15:14 #1
- 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]
-
Nov 4, 2008, 15:51 #2
- 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:
It works but is there a better way?
-
Nov 4, 2008, 17:06 #3
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Nov 5, 2008, 06:35 #4
- Join Date
- May 2005
- Posts
- 172
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Nov 5, 2008, 08:08 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
well, for one thing, it only goes after the images table once instead of twice
-
Nov 13, 2008, 08:04 #6
- 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?
-
Nov 13, 2008, 08:07 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
with joins, similar to post #3
give it a try
-
Nov 13, 2008, 08:11 #8
- 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
-
Nov 13, 2008, 08:16 #9
- 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
-
Nov 13, 2008, 08:21 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Nov 13, 2008, 08:29 #11
- 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, 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
-
Nov 13, 2008, 08:44 #12
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Nov 13, 2008, 08:55 #13
- 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...
-
Nov 13, 2008, 09:02 #14
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Nov 13, 2008, 09:20 #15
- 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...?
-
Nov 13, 2008, 09:26 #16
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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?
-
Nov 13, 2008, 09:31 #17
- 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
-
Nov 13, 2008, 10:24 #18
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Nov 18, 2008, 07:08 #19
- 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?
-
Nov 19, 2008, 11:54 #20
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Nov 25, 2008, 13:45 #21
- Join Date
- May 2005
- Posts
- 172
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Nov 25, 2008, 13:51 #22
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Nov 25, 2008, 16:04 #23
- Join Date
- May 2005
- Posts
- 172
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
That worked perfectly, thanks for all your help.
Bookmarks