April 19th, 2013, 12:30 AM
-
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.
April 19th, 2013, 03:13 AM
-
GROUP_CONCAT works only in mysql
April 20th, 2013, 05:58 AM
-
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?
April 20th, 2013, 08:29 AM
-
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)