The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Databases
> MySQL Help
|
What is the correct way of writing this query?
Discuss What is the correct way of writing this query? in the MySQL Help forum on Dev Shed.
What is the correct way of writing this query? MySQL Help forum discussing administration, SQL syntax, and other MySQL-related topics. MySQL is an open-source relational database management system (RDBMS).
|
|
 |
|
|
|
|
Dev Shed Forums Sponsor:
|
|
|
July 29th, 2012, 07:11 PM
|
 |
A Change of Season
|
|
|
|
|
What is the correct way of writing this query?
What is the correct way of writing this query so it is valid in every Database system (not only mysql)?
My objective is to retrieve 1 hotel, 1 hotelroom and 1 package for currently logged supplier. Join via hotel_id in hotels, hotelrooms and packages table.
Choice A:
Code:
SELECT hotels.hotel_id,
hotels.hotelname AS HN,
hotelrooms.room_id AS room_id,
hotelrooms.roomname AS roomname,
packages.id AS pid,
packages.name AS pname,
hotels.status AS HSTAT
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.beta_supplier = 5
AND hotels.status = 'active'
GROUP BY hotels.hotel_id
ORDER BY hotels.hotelname,
packages.package_order DESC
Choice B)
Code:
SELECT hotels.hotel_id,
hotels.hotelname AS HN,
q_rooms.room_id AS room_id,
q_rooms.roomname AS roomname,
packages.id AS pid,
packages.name AS pname,
hotels.status AS HSTAT
FROM hotels
INNER JOIN (SELECT room_id,
hotel_id,
roomname
FROM hotelrooms) AS q_rooms
ON hotels.hotel_id = q_rooms.hotel_id
INNER JOIN packages
ON q_rooms.room_id = packages.room_id
WHERE hotels.beta_supplier = 5
AND hotels.status = 'active'
ORDER BY hotels.hotelname,
packages.package_order DESC LIMIT 1
Choice C)
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=1962 DEFAULT CHARSET=latin1
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=9244 DEFAULT CHARSET=latin1
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=187180 DEFAULT CHARSET=latin1
__________________
Devshed people, please fix the spell check:
System is temporarily busy. Please try again in a few seconds.
Last edited by zxcvbnm : July 29th, 2012 at 07:29 PM.
|
July 29th, 2012, 08:32 PM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
|
well, A and B are both definitely wrong
A violates proper groupung, and B uses LIMIT which is unique to mysql (and postgresql, i believe)
as for C, why do you care? just run either A or B if bofadem work for you in mysql
|
July 29th, 2012, 09:41 PM
|
|
A Change of Season
|
|
|
|
Quote: | Originally Posted by r937 As for C, why do you care? just run either A or B if bofadem work for you in mysql | Would you please share C that is no exclusive to mysql?
|
July 30th, 2012, 02:51 AM
|
|
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
Quote: | Originally Posted by zxcvbnm Would you please share C that is no exclusive to mysql? | on the assumption that there aren't a bazillion rooms in a given hotel, and there aren't a bazillion packages for each room, i would probably retrieve everything (so that would be query B but without the LIMIT) and then pick one row with my front end language
but that pre-supposes that i wanted to write a query that would work in all database systems...
... and i wouldn't do that
|
July 30th, 2012, 09:37 PM
|
|
A Change of Season
|
|
|
|
Quote: | Originally Posted by r937 on the assumption that there aren't a bazillion rooms in a given hotel, and there aren't a bazillion packages for each room, i would probably retrieve everything (so that would be query B but without the LIMIT) and then pick one row with my front end language. | Thanks Rudy. One more: About using LIMIT, regardless of number of records in the table, which one is true: - Query 1 (below) puts "less pressure" on the database and server and is "more optimized"
- Using LIMIT has nothing to do with it. These 2 queries use exact same amount of memory and have exact same amount of pressure on the database and the server.
Query 1
Code:
SELECT id FROM hotels
Query 2
Code:
SELECT id FROM hotels LIMIT 1
|
July 30th, 2012, 09:57 PM
|
|
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
Quote: | Originally Posted by zxcvbnm ... which one is true: | neither
|
July 30th, 2012, 10:05 PM
|
|
A Change of Season
|
|
|
|
Quote: | Originally Posted by r937 neither
| lol, I knew you're gonna say that. Ok would you please comment?
|
July 31st, 2012, 01:39 AM
|
|
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
Quote: | Originally Posted by zxcvbnm Ok would you please comment? |
sure, no problem
Quote: | Originally Posted by zxcvbnm Query 1
Code:
SELECT id FROM hotels
Query 2
Code:
SELECT id FROM hotels LIMIT 1
| query 1 returns as many ids as there are rows in the table, while query 2 returns only one
i don't know how you can think that query 1 is more optimized or exerts less pressure on the database
|
July 31st, 2012, 05:26 AM
|
|
Contributing User
|
|
Join Date: Jun 2012
Location: Wisconsin
Posts: 78
Time spent in forums: 1 Day 11 h 51 m 6 sec
Reputation Power: 1
|
|
|
my guess on using limit is it will grab results up untell it hits the limit. instead of grabbing the whole table then showing 1 result
|
July 31st, 2012, 06:50 AM
|
|
|
>my guess on using limit is it will grab results up untell it hits the limit. instead of grabbing the whole table then showing 1 result
And you're quite right... some of the time!
Last edited by cafelatte : July 31st, 2012 at 06:58 AM.
|
August 1st, 2012, 12:45 AM
|
|
A Change of Season
|
|
|
|
Look like LIMIT has absolutely nothing to do with it! The EXPLAINs are exatcly the same!
Edit:
Same as SELECTED columns! No matter how many columns I SELECT, the EXPLAIN looks exatly the same.
Last edited by zxcvbnm : August 1st, 2012 at 12:49 AM.
|
August 1st, 2012, 01:45 AM
|
 |
Hockey face
|
|
Join Date: Nov 2001
Location: St. Catharines, Canada
|
|
|
Of course the explain plan would look the same. You have no join, no subquery, no where clause.
But tell me do you really think the execution time would be the same for both queries if you have 100,000 rows in the table? what about 25 million rows?
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
