[English Breakfast Tea]

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?

 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

[r937]

GROUP_CONCAT works only in mysql

[English Breakfast Tea]

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?

[r937]

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)