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

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    I used MIN(packages.id) as packages and hotles are 1 to many relationshio and it returned only 1 row
    Code:
    SELECT ALLOTMENTS_SQL.mm AS TOTAL,
           hotels.hotelname,
           packages.name,
           MIN(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'
    ORDER  BY hotels.startype DESC
    and if I add GROUP BY hotels.hotel_id to it looks like I get the right results.

    I kindly ask you not to give up on me.

    Thank you


    ---------

    Downloading SimplySql to read it.
    Last edited by zxcvbnm; February 23rd, 2013 at 01:08 AM.
  2. #17
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by zxcvbnm
    and if I add GROUP BY hotels.hotel_id to it looks like I get the right results.
    well, maybe not "right" but getting there

    consider that there are multiple packages per hotel, you are getting the lowest package id for each hotel

    but package name and package low are still not aggregated, so they'll have a random value from amongst all the packages for that hotel... and not necessarily the same package as the one corresponding to MIN(packages.id)

    you beginning to get a feel for one-to-many relationships yet?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #18
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    In that case the first thing that comes to my mind is to use MIN for other packages.name and packages.low as well but that does not feel right. What do you recommend?
    Code:
    SELECT ALLOTMENTS_SQL.mm AS TOTAL,
           hotels.hotelname,
           MIN(packages.name) as name,
           MIN(packages.id)       AS PPIIDD,
           MIN(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'
    ORDER  BY hotels.startype DESC
  6. #19
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by zxcvbnm
    What do you recommend?
    at this point, not much

    see that subquery called ALLOTMENTS_SQL? it produces one row for each package

    how does a package relate to a hotel?

    i'm lost
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #20
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Code:
     CREATE TABLE `packages`
      (
         `id`                       INT(11) NOT NULL auto_increment,
         `onsale_box`               INT(11) NOT NULL DEFAULT '0',
         `package_order`            INT(1) NOT NULL,
         `hot_deal`                 INT(11) NOT NULL,
         `red_hot_deal`             INT(11) NOT NULL,
         `stop_sell`                INT(11) NOT NULL,
         `emails_receiver`          VARCHAR(255) NOT NULL,
         `status`                   VARCHAR(255) NOT NULL,
         `name`                     VARCHAR(55) NOT NULL,
         `minimum_number_of_nights` INT(11) NOT NULL,
         `type`                     VARCHAR(55) NOT NULL,
         `hotel_id`                 INT(11) NOT NULL,
         `room_id`                  INT(11) NOT NULL,
         `suppiler_id`              INT(11) NOT NULL,
         `edit_history`             TEXT NOT NULL,
         `added_by`                 VARCHAR(250) NOT NULL,
         `low`                      INT(11) NOT NULL,
         `shoulder`                 INT(11) NOT NULL,
         `high`                     INT(11) NOT NULL,
         `weekend_low`              INT(11) NOT NULL,
         `weekend_shoulder`         INT(11) NOT NULL,
         `weekend_high`             INT(11) NOT NULL,
         `special_events`           INT(11) NOT NULL,
         `date_added`               VARCHAR(55) NOT NULL,
         `description`              TEXT NOT NULL,
         `days_of_year`             VARCHAR(255) NOT NULL,
         `low_advertise`            VARCHAR(11) NOT NULL,
         `shoulder_advertise`       VARCHAR(11) NOT NULL,
         `high_advertise`           VARCHAR(11) NOT NULL,
         `special_advertise`        VARCHAR(11) NOT NULL,
         `low_min_nights`           INT(11) NOT NULL,
         `shoulder_min_nights`      INT(11) NOT NULL,
         `high_min_nights`          INT(11) NOT NULL,
         `special_min_nights`       INT(11) NOT NULL,
         `history`                  TEXT NOT NULL,
         `sent_to_blog`             INT(11) NOT NULL,
         `requirements`             TEXT NOT NULL,
         `start_date`               DATE NOT NULL,
         `end_date`                 DATE NOT NULL,
         PRIMARY KEY (`id`),
         KEY `hotel_id_index` (`hotel_id`),
         KEY `room_id` (`room_id`),
         KEY `type` (`type`),
         KEY `status` (`status`)
      )
    engine=myisam
    auto_increment=9377
    DEFAULT charset=latin1
    Code:
     CREATE TABLE `hotels`
      (
         `hotel_id`                            INT(10) UNSIGNED NOT NULL
         auto_increment,
         `beta_supplier`                       INT(11) NOT NULL,
         `commision`                           FLOAT NOT NULL,
         `allotz_id`                           INT(11) UNSIGNED NOT NULL,
         `hotelname`                           VARCHAR(200) NOT NULL DEFAULT '',
         `country_id`                          INT(10) UNSIGNED NOT NULL DEFAULT '0'
         ,
         `region_id`                           INT(10) UNSIGNED NOT NULL
         DEFAULT '0',
         `destination_id`                      INT(10) UNSIGNED NOT NULL DEFAULT '0'
         ,
         `places_id`                           INT(11) NOT NULL,
         `accomodationtype`                    VARCHAR(255) NOT NULL DEFAULT 'Hotel'
         ,
         `startype`                            VARCHAR(45) NOT NULL DEFAULT ''
         comment 'This would be image name',
         `ratingstd`                           VARCHAR(20) NOT NULL DEFAULT '',
         `abn`                                 VARCHAR(45) NOT NULL DEFAULT '',
         `address1`                            VARCHAR(200) NOT NULL DEFAULT '',
         `address2`                            VARCHAR(200) NOT NULL DEFAULT '',
         `town`                                VARCHAR(100) NOT NULL DEFAULT '',
         `postcode`                            VARCHAR(10) NOT NULL DEFAULT '',
         `adminphone`                          VARCHAR(100) NOT NULL DEFAULT '',
         `adminfax`                            VARCHAR(50) NOT NULL DEFAULT '',
         `acc_contactname`                     VARCHAR(100) NOT NULL DEFAULT '',
         `acc_phone`                           VARCHAR(100) NOT NULL DEFAULT '',
         `acc_fax`                             VARCHAR(50) NOT NULL DEFAULT '',
         `acc_email`                           VARCHAR(50) NOT NULL DEFAULT '',
         `res_contactname`                     VARCHAR(100) NOT NULL DEFAULT '',
         `res_phone`                           VARCHAR(100) NOT NULL DEFAULT '',
         `res_fax`                             VARCHAR(50) NOT NULL DEFAULT '',
         `res_email`                           VARCHAR(50) NOT NULL DEFAULT '',
         `sale_contactname`                    VARCHAR(100) NOT NULL DEFAULT '',
         `sale_phone`                          VARCHAR(100) NOT NULL DEFAULT '',
         `sale_email`                          VARCHAR(50) NOT NULL DEFAULT '',
         `mgm_contactname`                     VARCHAR(100) NOT NULL DEFAULT '',
         `mgm_phone`                           VARCHAR(50) NOT NULL DEFAULT '',
         `mgm_email`                           VARCHAR(50) NOT NULL DEFAULT '',
         `property_desc`                       TEXT,
         `quickdesc`                           TEXT NOT NULL,
         `noofrooms`                           INT(11) DEFAULT '0',
         `localattractions`                    TEXT,
         `businessfacilities`                  TEXT,
         `accept_child`                        VARCHAR(20) DEFAULT '0',
         `child_friendly`                      VARCHAR(20) DEFAULT '0',
         `nonsmoking`                          VARCHAR(20) DEFAULT '0',
         `businessfriendly`                    SMALLINT(6) DEFAULT '0',
         `amen_24hrrecp`                       SMALLINT(6) DEFAULT '0',
         `amen_lift`                           SMALLINT(6) DEFAULT '0',
         `amen_24hrroomsvc`                    SMALLINT(6) DEFAULT '0',
         `amen_messctr`                        SMALLINT(6) DEFAULT '0',
         `amen_pets`                           SMALLINT(6) DEFAULT '0',
         `amen_playgrd`                        SMALLINT(6) DEFAULT '0',
         `amen_bbq`                            SMALLINT(6) DEFAULT '0',
         `amen_heatedpool`                     SMALLINT(6) DEFAULT '0',
         `amen_busctr`                         SMALLINT(6) DEFAULT '0',
         `amen_outdoorpool`                    SMALLINT(6) DEFAULT '0',
         `amen_carhire`                        SMALLINT(6) DEFAULT '0',
         `amen_casino`                         SMALLINT(6) DEFAULT '0',
         `amen_transpt`                        SMALLINT(6) DEFAULT '0',
         `amen_chroom`                         SMALLINT(6) DEFAULT '0',
         `amen_rest`                           SMALLINT(6) DEFAULT '0',
         `amen_childclub`                      SMALLINT(6) DEFAULT '0',
         `amen_roomsvc`                        SMALLINT(6) DEFAULT '0',
         `amen_conci`                          SMALLINT(6) DEFAULT '0',
         `amen_safedepo`                       SMALLINT(6) DEFAULT '0',
         `amen_conference`                     SMALLINT(6) DEFAULT '0',
         `amen_sauna`                          SMALLINT(6) DEFAULT '0',
         `amen_securepark`                     SMALLINT(6) DEFAULT '0',
         `amen_spa`                            SMALLINT(6) DEFAULT '0',
         `amen_drycleaning`                    SMALLINT(6) DEFAULT '0',
         `amen_tab`                            SMALLINT(6) DEFAULT '0',
         `amen_games`                          SMALLINT(6) DEFAULT '0',
         `amen_tennis`                         SMALLINT(6) DEFAULT '0',
         `amen_kitchen`                        SMALLINT(6) DEFAULT '0',
         `amen_tourdesk`                       SMALLINT(6) DEFAULT '0',
         `amen_giftshop`                       SMALLINT(6) DEFAULT '0',
         `amen_valetpark`                      SMALLINT(6) DEFAULT '0',
         `amen_golf`                           SMALLINT(6) DEFAULT '0',
         `amen_vending`                        SMALLINT(6) DEFAULT '0',
         `amen_gym`                            SMALLINT(6) DEFAULT '0',
         `amen_water`                          SMALLINT(6) DEFAULT '0',
         `amen_interconn`                      SMALLINT(6) DEFAULT '0',
         `amen_cablesatt`                      SMALLINT(6) DEFAULT '0',
         `amen_internet`                       SMALLINT(6) NOT NULL DEFAULT '0',
         `checkintime`                         VARCHAR(6) NOT NULL DEFAULT '',
         `checkouttime`                        VARCHAR(6) NOT NULL DEFAULT '',
         `disttocityctr`                       DOUBLE(10, 2) NOT NULL DEFAULT '0.00'
         ,
         `nearestairport`                      VARCHAR(100) NOT NULL DEFAULT
         '',
         `instructions`                        TEXT,
         `distancetoairport`                   VARCHAR(255) NOT NULL DEFAULT '',
         `location`                            TEXT,
         `vehiclehire`                         TEXT,
         `carparking`                          TEXT,
         `airportpickup`                       SMALLINT(5) UNSIGNED NOT NULL DEFAULT
         '0',
         `pickupdetail`                        TEXT,
         `reserv_cancel_policy`                TEXT,
         `accountbalsettle`                    VARCHAR(100) NOT NULL DEFAULT '',
         `googlemap`                           VARCHAR(50) NOT NULL DEFAULT '',
         `username`                            VARCHAR(200) NOT NULL DEFAULT '',
         `password`                            VARCHAR(50) NOT NULL DEFAULT '',
         `mainimage`                           VARCHAR(50) NOT NULL DEFAULT '',
         `rest_alcbev`                         VARCHAR(50) NOT NULL DEFAULT '',
         `rest_name`                           VARCHAR(200) NOT NULL DEFAULT '',
         `rest_description`                    TEXT,
         `rest_menu`                           TEXT,
         `rest_openhour`                       VARCHAR(75) NOT NULL DEFAULT '',
         `rest_opendays`                       VARCHAR(100) NOT NULL DEFAULT '',
         `rest_image1`                         VARCHAR(50) NOT NULL DEFAULT '',
         `rest_image2`                         VARCHAR(50) NOT NULL DEFAULT '',
         `rest_image3`                         VARCHAR(50) NOT NULL DEFAULT '',
         `accbalsetdetail`                     TEXT,
         `amen_nighteveenter`                  SMALLINT(5) UNSIGNED DEFAULT '0',
         `pdf`                                 VARCHAR(50) NOT NULL DEFAULT '',
         `status`                              VARCHAR(10) NOT NULL DEFAULT '',
         `cancellationpolicy`                  TEXT NOT NULL,
         `paymentpolocy`                       TEXT NOT NULL,
         `dateadded`                           DATE NOT NULL DEFAULT '0000-00-00',
         `weekends`                            ENUM('2days', '3days') DEFAULT
         '2days',
         `locked`                              TINYINT(4) NOT NULL DEFAULT '1'
         comment 'Lock/Unlock flag',
         `last_alert`                          TIMESTAMP NOT NULL DEFAULT
         CURRENT_TIMESTAMP,
         `tags`                                TINYTEXT NOT NULL,
         `rank`                                TINYINT(4) UNSIGNED NOT NULL DEFAULT
         '0' comment 'TOP 10 Rank ',
         `share`                               TINYINT(1) NOT NULL DEFAULT '1',
         `step_one_complete`                   INT(11) NOT NULL,
         `step_two_complete`                   INT(11) NOT NULL,
         `step_three_complete`                 INT(11) NOT NULL,
         `step_four_complete`                  INT(11) NOT NULL,
         `history`                             TEXT NOT NULL,
         `sent_to_blog`                        INT(11) NOT NULL,
         `lat`                                 FLOAT NOT NULL,
         `lng`                                 FLOAT NOT NULL,
         `currency_code`                       VARCHAR(4) DEFAULT NULL,
         `language_code`                       VARCHAR(4) DEFAULT NULL,
         `banking_details`                     TEXT NOT NULL,
         `channel_manager_id`                  INT(11) NOT NULL,
         `channel_manager_name`                VARCHAR(255) NOT NULL,
         `channel_hotel_id`                    INT(11) NOT NULL,
         `hotel_id_in_channel_manager_website` INT(11) NOT NULL,
         `channel_hotel_status`                VARCHAR(255) NOT NULL,
         PRIMARY KEY (`hotel_id`),
         KEY `username` (`username`),
         KEY `hotels_ix_beta_supplier` (`beta_supplier`),
         KEY `hotelname_ix` (`hotelname`),
         KEY `status` (`status`),
         KEY `destination_id` (`destination_id`)
      )
    engine=myisam
    auto_increment=10005
    DEFAULT charset=latin1
    Last edited by zxcvbnm; February 23rd, 2013 at 07:05 PM.
  10. #21
  11. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Package relates to a hotel using hotel_id column in both tables.

    Do you need any other tables?

    Thank you
    Last edited by zxcvbnm; February 23rd, 2013 at 07:38 PM.
  12. #22
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by zxcvbnm
    Do you need any other tables?
    no, because i don't feel like going on

    does your query currently produce correct results?

    then i wouldn't mess it up any further
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #23
  15. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by r937
    Does your query currently produce correct results?then i wouldn't mess it up any further
    1- Please do not give up on this thread. It's so close. 2 - No the query is very strangely showing wrong results. The first part of the query shows the correct result and returns only 1 row (cheapest rate):
    Code:
    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-26'
                   AND allotments_new.day_date < '2013-02-27'
                   AND allotments_new.day_rate > 0
                   AND allotments_new.minimum_number_of_nights <= 1
            GROUP  BY allotments_new.package_id) AS Q_ONE
    But when I call the value from the main query it gives me the wrong resut (SELECT ALLOTMENTS_SQL.mm AS TOTAL,).

    And so the join between them is wrong! Look at the value for first column TOTAL and the 5th column PLOW.

    TOTAL is supposed to be the same as PLOW!
    Thank you Rudy
    Last edited by zxcvbnm; February 24th, 2013 at 06:02 PM.
  16. #24
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by zxcvbnm
    And so the join between them is wrong!
    if you're joining on packages_id to packages.id, then the join is correct

    however, if you're still grouping only on hotel_id and relying on the mysql GROUP BY hack to magically pull only one package...

    i want you to go back and reassess exactly what you want this query to do

    perhaps it should be broken up into more than one query

    your alternative is to keep plugging away until you get the correct GROUP BY syntax -- see post #2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #25
  19. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Hey Rudy thanks for getting back to me.

    I know exactlly what I want but because of my lack of knowledge of SQL I am not sure if what I want it possible or not using only one query.

    If I want to write this query properly without anyones help, I have to finish some chapters in your book first.

    For now I think I have to find a solution usng 2 queries.

    Thank you.
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo