#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

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

    Join query shows wrong results


    Hello;

    Why do I see the wrong results? There are 2 rooms and 6 packages. I see 12 and 12!
    Code:
     SELECT Count(hotelrooms.room_id) AS rooms,
           Count(packages.id)        AS packages
    FROM   hotels
           LEFT OUTER JOIN hotelrooms
                        ON hotels.hotel_id = hotelrooms.hotel_id
           LEFT OUTER JOIN packages
                        ON hotelrooms.room_id = packages.room_id
    WHERE  hotels.hotel_id = 1875
           AND hotels.status = 'active'
           AND packages.status = 'active'
           AND hotelrooms.status = 'active'
    Hotels
    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=10011 DEFAULT CHARSET=latin1
    Hotelrooms
    Code:
    CREATE TABLE `hotelrooms` (  `room_id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `maximum_number_of_adults_and_children` int(11) NOT NULL,  `room_rtype` varchar(255) NOT NULL,  `hotel_id` int(10) unsigned NOT NULL DEFAULT '0',  `allotz_room_id` int(10) unsigned NOT NULL DEFAULT '0',  `allotz_promo` int(10) unsigned NOT NULL DEFAULT '0',  `roomname` varchar(100) NOT NULL DEFAULT '',  `roomdescription` text NOT NULL,  `quickroomdescription` text NOT NULL,  `image1` varchar(50) NOT NULL DEFAULT '',  `image2` varchar(50) NOT NULL DEFAULT '',  `image3` varchar(50) NOT NULL DEFAULT '',  `maxnoofguests` int(10) unsigned NOT NULL DEFAULT '0',  `chargeperguest` double(10,2) NOT NULL DEFAULT '0.00',  `childrenpolicy` text NOT NULL,  `infantpolicy` text NOT NULL,  `pos` int(10) unsigned DEFAULT '0',  `noofrooms` int(11) NOT NULL DEFAULT '0',  `availableroom` int(11) NOT NULL DEFAULT '0',  `status` varchar(10) NOT NULL DEFAULT 'active',  `edit_history` text NOT NULL,  `timestamp` int(11) NOT NULL,  `date_added` date NOT NULL,  `total_number_children` int(11) NOT NULL,  `total_number_adults` int(11) NOT NULL,  `max_child_age` int(11) NOT NULL,  `channel_room_id` int(11) NOT NULL,  `channel_room_status` varchar(255) NOT NULL,  `channel_manager_id` int(11) NOT NULL,  PRIMARY KEY (`room_id`),  KEY `hotel_id_index` (`hotel_id`),  KEY `status` (`status`)) ENGINE=MyISAM AUTO_INCREMENT=187235 DEFAULT CHARSET=latin1
    Packages
    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=9390 DEFAULT CHARSET=latin1
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by zxcvbnm
    There are 2 rooms and 6 packages. I see 12 and 12!
    you answered your own question

    each room joins to the hotel, and then each package joins to each room

    i call this cross join effects

    it happens when there is more than one one-to-many join in the same query
    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,689
    Rep Power
    171
    Originally Posted by r937
    you answered your own question

    each room joins to the hotel, and then each package joins to each room

    i call this cross join effects

    it happens when there is more than one one-to-many join in the same query
    Great
    Whats the solution? Thanks
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by zxcvbnm
    Great
    Whats the solution? Thanks
    we've done this before

    push one of the joins down into a subquery into the FROM clause

    do a GROUP BY in the subquery, and something like GROUP_CONCAT so that the relationship returns one row per hotel, not many
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo