#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171

    Is it legal to use multiple GROUP_CONCAT functions in a subquery and group it also?


    Hello;

    I wrote a query that works fine in MySql. It is fast as well. But I dont have a way of checking if the query is valid in every database system or not.

    In general is it legal to use subqueries with multiple group by like this?
    Code:
     SELECT q_allotments.total,
           q_allotments.allotments_stats,
           q_allotments.allotments_nor,
           q_allotments.allotments_statuses,
           packages.id                  AS PPIIDD,
           hotelrooms.roomname,
           packages.name                AS PPNN,
           hotelrooms.room_id           AS RID,
           packages.description         AS PDESC,
           hotels.hotelname,
           regions.regionname,
           places.name                  AS PLACENAME,
           destinations.destinationname,
           hotels.property_desc,
           hotels.hotel_id              AS HID,
           room_rates.high              AS ROOM_HIGH,
           packages.low                 AS PLOW,
           hotels.startype,
           hotels.address1,
           hotels.address2,
           hotels.town,
           hotels.postcode,
           hotelrooms.room_id           AS RID,
           packages.type                AS PTYPE,
           countries.countryname        AS COUNTRY,
           regions.regionname           AS REGION_NAME,
           destinations.destinationname AS DESTINATION,
           hotels.destination_id,
           hotels.disttocityctr,
           hotels.distancetoairport,
           hotels.carparking,
           hotels.instructions
    FROM   packages
           INNER JOIN (SELECT allotments_new.package_id,
                              Sum(day_rate)                                AS TOTAL,
                              Group_concat(allotments_new.status)          AS
                                              ALLOTMENTS_STATS,
                              Group_concat(allotments_new.number_of_rooms) AS
                                              ALLOTMENTS_NOR,
                              Group_concat(allotments_new.status)          AS
                                              ALLOTMENTS_STATUSES
                       FROM   allotments_new
                       WHERE  allotments_new.day_date >= '2013-04-19'
                              AND allotments_new.day_date < '2013-04-22'
                              AND allotments_new.day_rate > 0
                              AND allotments_new.status != 'n'
                              AND allotments_new.minimum_number_of_nights <= 3
                       GROUP  BY package_id) AS q_allotments
                   ON q_allotments.package_id = packages.id
           LEFT OUTER JOIN hotels
                        ON hotels.hotel_id = packages.hotel_id
                           AND hotels.status = 'active'
           LEFT OUTER JOIN countries
                        ON hotels.country_id = countries.country_id
           LEFT OUTER JOIN hotelrooms
                        ON hotelrooms.room_id = packages.room_id
                           AND hotelrooms.status = 'active'
           LEFT OUTER JOIN room_rates
                        ON room_rates.room_id = hotelrooms.room_id
           LEFT OUTER JOIN destinations
                        ON destinations.destination_id = hotels.destination_id
           LEFT OUTER JOIN regions
                        ON regions.region_id = hotels.region_id
           LEFT OUTER JOIN places
                        ON places.id = hotels.places_id
    WHERE  hotels.hotel_id = '239'
           AND packages.status = 'active'
           AND hotels.status = 'active'
           AND hotelrooms.status = 'active'
           AND maximum_number_of_adults_and_children >= '1'
         
    GROUP  BY ppiidd
    ORDER  BY total
    Thanks
    Last edited by zxcvbnm; April 19th, 2013 at 12:57 AM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,143
    Rep Power
    4274
    GROUP_CONCAT works only in mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171
    Originally Posted by r937
    GROUP_CONCAT works only in mysql
    Any tips on how I can convert this query to one where is acceptable everywhere?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,143
    Rep Power
    4274
    Originally Posted by zxcvbnm
    Any tips on how I can convert this query to one where is acceptable everywhere?
    i don't think that's possible at all -- GROUP_CONCAT is insanely useful but there's no standard sql equivalent

    i would break the query down into multiple queries, and control the presentation of data using my application language (php or whatever)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo