Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

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

    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.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    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
    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,657
    Rep Power
    171
    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 ?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    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
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,657
    Rep Power
    171
    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
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    push the SUM subquery down a level, and use MIN in the next higher subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,657
    Rep Power
    171
    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
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,657
    Rep Power
    171
    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.
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    Originally Posted by zxcvbnm
    How are we doing Rudy? Please point any bad practice you see. Thank you
    what's the GROUP BY for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,657
    Rep Power
    171
    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.
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  24. #13
  25. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,657
    Rep Power
    171
    Let me think
  26. #14
  27. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,657
    Rep Power
    171
    ok, use aggregate function on which column exactly?
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo