SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    The Jellophonic Autobrain CHeeSeBLiND's Avatar
    Join Date
    Jul 2001
    Location
    Bolton, England
    Posts
    584
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    GROUP_CONCAT in MySQL 4: any alternative?

    OK, I have the following query running on a server with mysql 4.1+, but i need to get it to work on mysql4:

    Code:
    SELECT 
    					m.media_id, 
    					m.media_title, 
    					m.media_desc, 
    					m.media_thumb, (
    					SELECT count( * )
    					FROM comments
    					WHERE media_id = m.media_id
    					) AS num_comments, 
    					GROUP_CONCAT( COALESCE( t.tag_name ) , '' ) AS 'tags'
    				FROM media m
    				JOIN media_tags mt 
    					ON mt.media_id = m.media_id
    				JOIN tags t 
    					ON t.tag_id = mt.tag_id
    				WHERE m.media_approved =1
    					AND m.media_queued =0
    				GROUP BY m.media_id
    				ORDER BY m.media_date DESC
    Any suggestions?
    hmm...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    other than upgrade? nope

    by the way, this --

    GROUP_CONCAT( COALESCE( t.tag_name ) , '' )

    should be this --

    COALESCE( GROUP_CONCAT( t.tag_name ) , '' )

    because GROUP_CONCAT ignores nulls

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


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
  •