February 20th, 2013, 06:13 PM
-
Is this query valid?
I read often MySQL is loose compared to some other database systems, so I want to make sure my use of GROUP BY and JOINS are proper. So please comment on these 2:
1 - Regardless of namings, is this query written right for every database system? For example the use of GROUP BY, JOINS,....
2 - I am not 100% sure about the way I have used INNER SELECT. Is it correct? I know it's hard to say without knowing table structres but at a glance, what does it make you feel like? Flying or vomit?
Code:
SELECT ALLOTMENTS_SQL.s AS TOTAL,
hotels.hotelname,
packages.name,
packages.id AS PPIIDD,
packages.low AS PLOW,
hotels.startype,
hotels.address1,
hotels.address2,
hotels.town,
hotels.postcode,
hotels.property_desc,
hotels.hotel_id AS HID,
regions.regionname,
places.name AS PLACENAME,
destinations.destinationname
FROM packages
INNER JOIN hotels
ON hotels.hotel_id = packages.hotel_id
INNER JOIN destinations
ON destinations.destination_id = hotels.destination_id
INNER JOIN regions
ON regions.region_id = hotels.region_id
LEFT OUTER JOIN places
ON places.id = hotels.places_id
INNER JOIN (SELECT package_id,
Sum(day_rate) AS S
FROM allotments_new
WHERE allotments_new.day_date >= '2013-02-21'
AND allotments_new.day_date < '2013-02-25'
AND allotments_new.day_rate > 0
AND allotments_new.minimum_number_of_nights <= 1
GROUP BY allotments_new.package_id
ORDER BY s DESC) AS ALLOTMENTS_SQL
ON ALLOTMENTS_SQL.package_id = packages.id
WHERE hotels.destination_id = 18
GROUP BY hotels.hotel_id
Thank you.
February 20th, 2013, 10:55 PM
-
Originally Posted by zxcvbnm
1 - Regardless of namings, is this query written right for every database system? For example the use of GROUP BY, JOINS,....
your GROUP BY would be invalid in any other database system besides mysql, because there are non-aggregate expressions in the SELECT clause that are "hidden" from the GROUP BY clause
see http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html
Originally Posted by zxcvbnm
2 - I am not 100% sure about the way I have used INNER SELECT.
it's okay except for the ORDER BY which will be ignored and should be removed
February 21st, 2013, 01:52 AM
-
Originally Posted by r937
your GROUP BY would be invalid in any other database system besides mysql, because there are non-aggregate expressions in the SELECT clause that are "hidden" from the GROUP BY clause
see http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html
it's okay except for the ORDER BY which will be ignored and should be removed
But how can I order by s ?
February 21st, 2013, 02:25 AM
-
Originally Posted by zxcvbnm
But how can I order by s ?
It does not make any sense to use an order by in a derived table. So you don't need it there.
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
http://forums.devshed.com/misc.php?do=bbcode#code
Tips on how to ask better questions:
http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
http://wiki.postgresql.org/wiki/SlowQueryQuestions
http://catb.org/esr/faqs/smart-questions.html
February 21st, 2013, 04:12 PM
-
Originally Posted by shammat
It does not make any sense to use an order by in a derived table. So you don't need it there.
I really like to understand this
So I'm gonna try to ask it in another way:
What about the times that the sub-query returns more than 1 row and 1 of the retrieved columns is the result of SUM(some_column) and I want to choose the row with smallest SUM(some_column)?
As you can see it adds all the day_rates of the table together. I want to get the cheapest of those dates, makes sense? I hope so
February 21st, 2013, 04:20 PM
-
push the SUM subquery down a level, and use MIN in the next higher subquery
February 21st, 2013, 07:36 PM
-
Originally Posted by r937
push the SUM subquery down a level, and use MIN in the next higher subquery
Does this make sense to start this way?
Code:
SELECT Q_ONE.package_id, MIN( Q_ONE.S )
FROM (
SELECT package_id, Sum( day_rate ) AS S
FROM allotments_new
WHERE allotments_new.day_date >= '2013-02-21'
AND allotments_new.day_date < '2013-02-25'
AND allotments_new.day_rate >0
AND allotments_new.minimum_number_of_nights <=1
AND allotments_new.hotel_id =4
GROUP BY allotments_new.package_id
) AS Q_ONE
GROUP BY package_id
February 21st, 2013, 08:12 PM
-
Originally Posted by zxcvbnm
Does this make sense to start this way
yes, excellent
now make this a subquery and join it to your other tables
February 21st, 2013, 09:06 PM
-
How are we doing Rudy? Please point any bad practice you see. Thank you
Code:
SELECT ALLOTMENTS_SQL.mm AS TOTAL,
hotels.hotelname,
packages.name,
packages.id AS PPIIDD,
packages.low AS PLOW,
hotels.startype,
hotels.address1,
hotels.address2,
hotels.town,
hotels.postcode,
hotels.property_desc,
hotels.hotel_id AS HID,
regions.regionname,
places.name AS PLACENAME,
destinations.destinationname
FROM packages
INNER JOIN hotels
ON hotels.hotel_id = packages.hotel_id
AND hotels.destination_id = 18
AND hotels.status = 'active'
INNER JOIN hotelrooms
ON hotelrooms.room_id = packages.room_id
AND hotelrooms.status = 'active'
INNER JOIN destinations
ON destinations.destination_id = hotels.destination_id
INNER JOIN room_rates
ON room_rates.room_id = hotelrooms.room_id
INNER JOIN regions
ON regions.region_id = hotels.region_id
LEFT OUTER JOIN places
ON places.id = hotels.places_id
INNER JOIN (SELECT Q_ONE.package_id,
Min(Q_ONE.s) AS MM
FROM (SELECT package_id,
Sum(day_rate) AS S
FROM allotments_new
WHERE allotments_new.day_date >= '2013-02-22'
AND allotments_new.day_date < '2013-02-23'
AND allotments_new.day_rate > 0
AND allotments_new.minimum_number_of_nights <=
1
GROUP BY allotments_new.package_id) AS Q_ONE
GROUP BY package_id) AS ALLOTMENTS_SQL
ON ALLOTMENTS_SQL.package_id = packages.id
WHERE packages.status = 'active'
GROUP BY hotels.hotel_id
ORDER BY hotels.startype DESC
Last edited by zxcvbnm; February 21st, 2013 at 10:47 PM.
February 22nd, 2013, 02:50 AM
-
Originally Posted by zxcvbnm
How are we doing Rudy? Please point any bad practice you see. Thank you
what's the GROUP BY for?
February 22nd, 2013, 05:00 AM
-
Originally Posted by r937
what's the GROUP BY for?
I want to see only one hotel_id. I dont want to see multiple rows with same hotel_id. Whne I remove GROUP BY I see multiple of same hotel_id.
February 22nd, 2013, 05:03 AM
-
Originally Posted by zxcvbnm
I want to see only one hotel_id. I dont want to see multiple rows with same hotel_id. Whne I remove GROUP BY I see multiple of same hotel_id.
okay, fine
so at least some of the other columns in the SELECT clause are in a one-to-many realationship with hotel_id, and you are happy to have a random choice from their values for the single hotel_id?
this is using mysql's GROUP BY extension as a hack
far better would be to use aggregate functions on those columns, like MIN or MAX or COUNT or SUM
February 22nd, 2013, 05:03 AM
-
February 22nd, 2013, 05:13 AM
-
ok, use aggregate function on which column exactly?
February 22nd, 2013, 06:42 AM
-
Originally Posted by zxcvbnm
ok, use aggregate function on which column exactly?
on any that aren't in a one-to-one relationship with hotel_id
for instance, all columns of the hotels table are by definition
also, presumably hotels to regions is a many-to-one relationship, so each hotel_id has only one region_id
what about the others?