[English Breakfast Tea]

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?

 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.

[r937]

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

[English Breakfast Tea]

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 ?

[shammat]

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.

[English Breakfast Tea]

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

[r937]

push the SUM subquery down a level, and use MIN in the next higher subquery

[English Breakfast Tea]

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?

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

[r937]

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

[English Breakfast Tea]

How are we doing Rudy? Please point any bad practice you see. Thank you

 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

[r937]

Originally Posted by zxcvbnm

How are we doing Rudy? Please point any bad practice you see. Thank you

what's the GROUP BY for?

[English Breakfast Tea]

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.

[r937]

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

[English Breakfast Tea]

Let me think

[English Breakfast Tea]

ok, use aggregate function on which column exactly?

[r937]

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?