Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query question

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.city
FROM RESERVATION r,
ROOM rm,
HOTEL h
WHERE 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 02:57:53
[code]
SELECT DISTINCT h.city
FROM RESERVATION r
INNER JOIN ROOM rm
ON rm.ROOM_NUM = r.ROOM_NUM
AND rm.IDH = r.IDH
AND r.DATE >= '20091030'
AND rm.type = 'single'
INNER JOIN HOTEL h
ON rm.IDH = h.IDH
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 07:14:47
[code]
SELECT h.city
FROM RESERVATION r
INNER JOIN ROOM rm
ON rm.ROOM_NUM = r.ROOM_NUM
AND rm.IDH = r.IDH
AND r.DATE >= '20091030'
INNER JOIN HOTEL h
ON rm.IDH = h.IDH
GROUP BY h.CITY
HAVING SUM(CASE WHEN rm.type = 'single' THEN 0 ELSE 1 END) =0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-11-07 : 07:18:49
[code]
SELECT H.City
FROM 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.City
HAVING MIN(CASE WHEN R.[type] = 'single' THEN 1 ELSE 0 END) = 1
[/code]
Go to Top of Page

11maxim11
Starting Member

7 Posts

Posted - 2013-11-07 : 09:13:42
is there a way without using the CASE/WHEN/THEN constructs?
Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-11-07 : 09:19:23
IIF ?
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 onwards
Also its equivalent to CASE..WHEN (internally it does the same thing)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

11maxim11
Starting Member

7 Posts

Posted - 2013-11-07 : 09:27:30
because is not supported by all DBMSs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 09:30:00
Anyways here you go

SELECT DISTINCT h.city
FROM RESERVATION r
INNER JOIN ROOM rm
ON rm.ROOM_NUM = r.ROOM_NUM
AND rm.IDH = r.IDH
AND r.DATE >= '20091030'
AND rm.type = 'single'
INNER JOIN HOTEL h
ON rm.IDH = h.IDH
WHERE NOT EXISTS (SELECT 1
FROM HOTEl h1
INNER JOIN ROOM r1
ON r1.IDH = h.IDH
WHERE h1.city = h.city
AND r1.type <> 'Single'
AND r1.DATE >= '20091030'
)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 onwards
Also its equivalent to CASE..WHEN (internally it does the same thing)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



I know that, no need to say.
Go to Top of Page

11maxim11
Starting Member

7 Posts

Posted - 2013-11-07 : 09:53:10
quote:
Originally posted by visakh16



SELECT DISTINCT h.city
FROM RESERVATION r
INNER JOIN ROOM rm
ON rm.ROOM_NUM = r.ROOM_NUM
AND rm.IDH = r.IDH
AND r.DATE >= '20091030'
AND rm.type = 'single'
INNER JOIN HOTEL h
ON rm.IDH = h.IDH
WHERE NOT EXISTS (SELECT 1
FROM HOTEl h1
INNER JOIN ROOM r1
ON r1.IDH = h.IDH
WHERE h1.city = h.city
AND 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!
Go to Top of Page

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 onwards
Also its equivalent to CASE..WHEN (internally it does the same thing)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



I know that, no need to say.


That was for the benefit of OP and others
And also just in case you're not aware as you suggested it when OP asked for an alternative for CASE ...WHEN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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.city
FROM RESERVATION r
INNER JOIN ROOM rm
ON rm.ROOM_NUM = r.ROOM_NUM
AND rm.IDH = r.IDH
AND r.DATE >= '20091030'
AND rm.type = 'single'
INNER JOIN HOTEL h
ON rm.IDH = h.IDH
WHERE NOT EXISTS (SELECT 1
FROM HOTEl h1
INNER JOIN ROOM r1
ON r1.IDH = h.IDH
WHERE h1.city = h.city
AND 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 right

SELECT DISTINCT h.city
FROM RESERVATION r
INNER JOIN ROOM rm
ON rm.ROOM_NUM = r.ROOM_NUM
AND rm.IDH = r.IDH
AND r.DATE >= '20091030'
AND rm.type = 'single'
INNER JOIN HOTEL h
ON rm.IDH = h.IDH
WHERE NOT EXISTS (SELECT 1
FROM HOTEl h1
INNER JOIN ROOM rm1
ON rm1.IDH = h.IDH
INNER JOIN RESERVATION r1
ON r1.ROOM_NUM = rm1.ROOM_NUM
AND r1.IDH = rm1.IDH
WHERE h1.city = h.city
AND rm1.type <> 'Single'
AND r1.DATE >= '20091030'
)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

11maxim11
Starting Member

7 Posts

Posted - 2013-11-07 : 11:50:36
quote:
Originally posted by visakh16
yes you're right

SELECT DISTINCT h.city
FROM RESERVATION r
INNER JOIN ROOM rm
ON rm.ROOM_NUM = r.ROOM_NUM
AND rm.IDH = r.IDH
AND r.DATE >= '20091030'
AND rm.type = 'single'
INNER JOIN HOTEL h
ON rm.IDH = h.IDH
WHERE NOT EXISTS (SELECT 1
FROM HOTEl h1
INNER JOIN ROOM rm1
ON rm1.IDH = h.IDH
INNER JOIN RESERVATION r1
ON r1.ROOM_NUM = rm1.ROOM_NUM
AND r1.IDH = rm1.IDH
WHERE h1.city = h.city
AND 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.
Go to Top of Page

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 visakh16
yes you're right

SELECT DISTINCT h.city
FROM RESERVATION r
INNER JOIN ROOM rm
ON rm.ROOM_NUM = r.ROOM_NUM
AND rm.IDH = r.IDH
AND r.DATE >= '20091030'
AND rm.type = 'single'
INNER JOIN HOTEL h
ON rm.IDH = h.IDH
WHERE NOT EXISTS (SELECT 1
FROM HOTEl h1
INNER JOIN ROOM rm1
ON rm1.IDH = h.IDH
INNER JOIN RESERVATION r1
ON r1.ROOM_NUM = rm1.ROOM_NUM
AND r1.IDH = rm1.IDH
WHERE h1.city = h.city
AND 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

11maxim11
Starting Member

7 Posts

Posted - 2013-11-07 : 13:07:33
quote:
Originally posted by visakh16
its 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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 13:10:37
yep..that you can dispense with

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -