Author |
Topic |
11maxim11
Starting Member
7 Posts |
Posted - 2013-11-06 : 19:29:59
|
Hi all,the tables are: CUSTOMER (IDC, Name)HOTEL (IDH, city, country)ROOM (ROOM_NUM, IDH, type)RESERVATION (ROOM_NUM, IDH, DATE, idc)the PKs are in capital letters. Find the cities in which were booked, with the start date of 2009/10/30, *only* single rooms (type='single').may you help me with this query? |
|
jethrow
Starting Member
37 Posts |
Posted - 2013-11-06 : 20:43:01
|
[code]SELECT h.cityFROM RESERVATION r, ROOM rm, HOTEL hWHERE r.ROOM_NUM = rm.ROOM_NUM AND rm.IDH = h.IDH AND rm.type = 'single' AND DATE >= '2009/10/30'[/code]Microsoft SQL Server Noobie |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-07 : 02:57:53
|
[code]SELECT DISTINCT h.cityFROM RESERVATION rINNER JOIN ROOM rmON rm.ROOM_NUM = r.ROOM_NUMAND rm.IDH = r.IDHAND r.DATE >= '20091030'AND rm.type = 'single'INNER JOIN HOTEL hON rm.IDH = h.IDH[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
11maxim11
Starting Member
7 Posts |
Posted - 2013-11-07 : 05:14:06
|
thanks guys, but the result is not correct. I have to find the cities in which were reserved *only* single room, not at least one single room. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-07 : 07:14:47
|
[code]SELECT h.cityFROM RESERVATION rINNER JOIN ROOM rmON rm.ROOM_NUM = r.ROOM_NUMAND rm.IDH = r.IDHAND r.DATE >= '20091030'INNER JOIN HOTEL hON rm.IDH = h.IDHGROUP BY h.CITYHAVING SUM(CASE WHEN rm.type = 'single' THEN 0 ELSE 1 END) =0[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2013-11-07 : 07:18:49
|
[code]SELECT H.CityFROM Hotel H JOIN Room R ON H.IDH = R.IDH JOIN Reservation Re ON R.Room_Num = Re.Room_Num AND R.IDH = Re.RDH AND re.[date] = '20091030'GROUP BY H.CityHAVING MIN(CASE WHEN R.[type] = 'single' THEN 1 ELSE 0 END) = 1[/code] |
|
|
11maxim11
Starting Member
7 Posts |
Posted - 2013-11-07 : 09:13:42
|
is there a way without using the CASE/WHEN/THEN constructs? |
|
|
sigmas
Posting Yak Master
172 Posts |
Posted - 2013-11-07 : 09:19:23
|
IIF ? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-07 : 09:25:27
|
quote: Originally posted by 11maxim11 is there a way without using the CASE/WHEN/THEN constructs?
why whats the issue with that?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-07 : 09:27:14
|
quote: Originally posted by sigmas IIF ?
Only available from sql 2012 onwardsAlso its equivalent to CASE..WHEN (internally it does the same thing)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
11maxim11
Starting Member
7 Posts |
Posted - 2013-11-07 : 09:27:30
|
because is not supported by all DBMSs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-07 : 09:30:00
|
Anyways here you goSELECT DISTINCT h.cityFROM RESERVATION rINNER JOIN ROOM rmON rm.ROOM_NUM = r.ROOM_NUMAND rm.IDH = r.IDHAND r.DATE >= '20091030'AND rm.type = 'single'INNER JOIN HOTEL hON rm.IDH = h.IDHWHERE NOT EXISTS (SELECT 1FROM HOTEl h1INNER JOIN ROOM r1ON r1.IDH = h.IDHWHERE h1.city = h.cityAND r1.type <> 'Single'AND r1.DATE >= '20091030') ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-07 : 09:31:31
|
quote: Originally posted by 11maxim11 because is not supported by all DBMSs
Its supported in SQL Server which is what this forum is for. If you're using another DBMS please try in relevant forums to get specific help. Wherever possible we will try to give generic solutions however we cant always guarantee that it will work all types of DBMS.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sigmas
Posting Yak Master
172 Posts |
Posted - 2013-11-07 : 09:40:10
|
quote: Originally posted by visakh16
quote: Originally posted by sigmas IIF ?
Only available from sql 2012 onwardsAlso its equivalent to CASE..WHEN (internally it does the same thing)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
I know that, no need to say. |
|
|
11maxim11
Starting Member
7 Posts |
Posted - 2013-11-07 : 09:53:10
|
quote: Originally posted by visakh16
SELECT DISTINCT h.cityFROM RESERVATION rINNER JOIN ROOM rmON rm.ROOM_NUM = r.ROOM_NUMAND rm.IDH = r.IDHAND r.DATE >= '20091030'AND rm.type = 'single'INNER JOIN HOTEL hON rm.IDH = h.IDHWHERE NOT EXISTS (SELECT 1FROM HOTEl h1INNER JOIN ROOM r1ON r1.IDH = h.IDHWHERE h1.city = h.cityAND r1.type <> 'Single'AND r1.DATE >= '20091030')
I think miss a JOIN with RESERVATION in the sub-query.I'm working with more DBMSs, so I need SQL code as standard as possible.Anyway...thanks a lot! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-07 : 11:36:03
|
quote: Originally posted by sigmas
quote: Originally posted by visakh16
quote: Originally posted by sigmas IIF ?
Only available from sql 2012 onwardsAlso its equivalent to CASE..WHEN (internally it does the same thing)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
I know that, no need to say.
That was for the benefit of OP and othersAnd also just in case you're not aware as you suggested it when OP asked for an alternative for CASE ...WHEN------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-07 : 11:38:48
|
quote: Originally posted by 11maxim11
quote: Originally posted by visakh16
SELECT DISTINCT h.cityFROM RESERVATION rINNER JOIN ROOM rmON rm.ROOM_NUM = r.ROOM_NUMAND rm.IDH = r.IDHAND r.DATE >= '20091030'AND rm.type = 'single'INNER JOIN HOTEL hON rm.IDH = h.IDHWHERE NOT EXISTS (SELECT 1FROM HOTEl h1INNER JOIN ROOM r1ON r1.IDH = h.IDHWHERE h1.city = h.cityAND r1.type <> 'Single'AND r1.DATE >= '20091030')
I think miss a JOIN with RESERVATION in the sub-query.I'm working with more DBMSs, so I need SQL code as standard as possible.Anyway...thanks a lot!
yes you're rightSELECT DISTINCT h.cityFROM RESERVATION rINNER JOIN ROOM rmON rm.ROOM_NUM = r.ROOM_NUMAND rm.IDH = r.IDHAND r.DATE >= '20091030'AND rm.type = 'single'INNER JOIN HOTEL hON rm.IDH = h.IDHWHERE NOT EXISTS (SELECT 1FROM HOTEl h1INNER JOIN ROOM rm1ON rm1.IDH = h.IDHINNER JOIN RESERVATION r1ON r1.ROOM_NUM = rm1.ROOM_NUMAND r1.IDH = rm1.IDHWHERE h1.city = h.cityAND rm1.type <> 'Single'AND r1.DATE >= '20091030') ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
11maxim11
Starting Member
7 Posts |
Posted - 2013-11-07 : 11:50:36
|
quote: Originally posted by visakh16yes you're rightSELECT DISTINCT h.cityFROM RESERVATION rINNER JOIN ROOM rmON rm.ROOM_NUM = r.ROOM_NUMAND rm.IDH = r.IDHAND r.DATE >= '20091030'AND rm.type = 'single'INNER JOIN HOTEL hON rm.IDH = h.IDHWHERE NOT EXISTS (SELECT 1FROM HOTEl h1INNER JOIN ROOM rm1ON rm1.IDH = h.IDHINNER JOIN RESERVATION r1ON r1.ROOM_NUM = rm1.ROOM_NUMAND r1.IDH = rm1.IDHWHERE h1.city = h.cityAND rm1.type <> 'Single'AND r1.DATE >= '20091030')
I was wondering if it was really necessary to put the conditions (r.DATE >= '20091030' AND rm.type = 'single') in the external query too. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-07 : 12:03:13
|
quote: Originally posted by 11maxim11
quote: Originally posted by visakh16yes you're rightSELECT DISTINCT h.cityFROM RESERVATION rINNER JOIN ROOM rmON rm.ROOM_NUM = r.ROOM_NUMAND rm.IDH = r.IDHAND r.DATE >= '20091030'AND rm.type = 'single'INNER JOIN HOTEL hON rm.IDH = h.IDHWHERE NOT EXISTS (SELECT 1FROM HOTEl h1INNER JOIN ROOM rm1ON rm1.IDH = h.IDHINNER JOIN RESERVATION r1ON r1.ROOM_NUM = rm1.ROOM_NUMAND r1.IDH = rm1.IDHWHERE h1.city = h.cityAND rm1.type <> 'Single'AND r1.DATE >= '20091030')
I was wondering if it was really necessary to put the conditions (r.DATE >= '20091030' AND rm.type = 'single') in the external query too.
its necessary so far as you want consider books only in the specified period------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
11maxim11
Starting Member
7 Posts |
Posted - 2013-11-07 : 13:07:33
|
quote: Originally posted by visakh16its necessary so far as you want consider books only in the specified period
right! the constraint on the date is necessary, but what about the type of room? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-07 : 13:10:37
|
yep..that you can dispense with------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Previous Page&nsp;
Next Page
|