1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Query to Get Each Category and it's latest Post

Discussion in 'MySQL' started by megamoose, Jul 3, 2010.

  1. #1
    Hey guys, having a bit of a problem all day with this one hopefully someone can help.

    Basically I have two tables. One called gallery_category and another called gallery_images

    What I want to do is bring back each category with it's latest post.

    This is what I have at the moment:
    
    			SELECT *
    			FROM `{PF}gallery_category` 
    			LEFT JOIN (SELECT *
    						FROM  `gallery_images` 
    				  ) v
    			ON `category_id` = `image_category`
    
    Code (markup):
    The problem I am having is it brings back each row with it's category. So I have the results:

    
    category_id	category_parent	category_name	category_description	image_id	image_name	image_path	image_thumbnail	image_original	image_category	image_date
    1	0	Example Category One	This is an example category for testing the new ga...	2	Random	2.jpg	2.thumb.jpg	http://i.ehow.com/images/a04/d2/0u/random-acts-kin...	1	2010-07-03 13:26:18
    1	0	Example Category One	This is an example category for testing the new ga...	1	Morer	1.jpg	1.thumb.jpg	http://i.ehow.com/images/a04/d2/0u/random-acts-kin...	1	2010-07-03 13:26:49
    2	0	Example Category Two	This is another example category for testing the n...	NULL	NULL	NULL	NULL	NULL	NULL	NULL
    3	1	Test Subcategory	This is a test sub category	3	Another	3.jpg	3.thumb.jpg	http://i.ehow.com/images/a04/d2/0u/random-acts-kin...	3	2010-07-03 15:57:22
    
    Code (markup):
    So it's bringing back each category twice. I am wanting it to just bring back each category once, with the most recent image in a left join.

    Thanks for any help guys.
     
    megamoose, Jul 3, 2010 IP
  2. megamoose

    megamoose Well-Known Member

    Messages:
    71
    Likes Received:
    1
    Best Answers:
    1
    Trophy Points:
    105
    #2
    What may make more sense actually is:

    From my list of images, how can I bring back the most recent (image_date) image for each category.


    I think I have worked out:
    
    			SELECT *
    			FROM  `gallery_category`
    			LEFT JOIN (    SELECT * FROM `gallery_images` ORDER BY `image_date` DESC
    			) v
    			ON image_category = category_id
    			WHERE `category_parent` = ''
    			GROUP BY category_id
    
    Code (markup):
    But the only problem is I'm worried this is quite a long query and with a large database it might take quite a while to process. Could this be simplified!
     
    Last edited: Jul 3, 2010
    megamoose, Jul 3, 2010 IP
  3. georgiivanov

    georgiivanov Member

    Messages:
    62
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    25
    #3
    Try this statement:

    where image_id is the field which MAX value you want.
     
    georgiivanov, Jul 6, 2010 IP
  4. megamoose

    megamoose Well-Known Member

    Messages:
    71
    Likes Received:
    1
    Best Answers:
    1
    Trophy Points:
    105
    #4
    georgiivanov, thanks very much for getting back to me on this. It' is much appreciated.

    You have solved it. Or at pretty much did. I am now using this query:
    SELECT gc.category_id, MAX(gi.image_date), image_link
    FROM `gallery_category` gc, `gallery_images` gi
    WHERE gc.category_id = gi.image_category
    GROUP BY gc.category_id
    Code (markup):
    It does the same as you're only orders it my the MAX date, and you have taught me how to use the MAX and GROUP BY effectivly. Something I probably should have learnt a long time ago :p

    Thanks mate!
     
    megamoose, Jul 15, 2010 IP