SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query question
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

11maxim11
Starting Member

7 Posts

Posted - 11/06/2013 :  19:29:59  Show Profile  Reply with Quote
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

USA
37 Posts

Posted - 11/06/2013 :  20:43:01  Show Profile  Reply with Quote
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'


Microsoft SQL Server Noobie

Edited by - jethrow on 11/06/2013 20:44:14
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/07/2013 :  02:57:53  Show Profile  Reply with Quote

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


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

11maxim11
Starting Member

7 Posts

Posted - 11/07/2013 :  05:14:06  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 11/07/2013 :  07:14:47  Show Profile  Reply with Quote

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


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

Ifor
Aged Yak Warrior

548 Posts

Posted - 11/07/2013 :  07:18:49  Show Profile  Reply with Quote

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

11maxim11
Starting Member

7 Posts

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

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 11/07/2013 :  09:19:23  Show Profile  Reply with Quote
IIF ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/07/2013 :  09:25:27  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 11/07/2013 :  09:27:14  Show Profile  Reply with Quote
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 - 11/07/2013 :  09:27:30  Show Profile  Reply with Quote
because is not supported by all DBMSs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/07/2013 :  09:30:00  Show Profile  Reply with Quote
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

Edited by - visakh16 on 11/07/2013 09:33:27
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/07/2013 :  09:31:31  Show Profile  Reply with Quote
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

Belarus
172 Posts

Posted - 11/07/2013 :  09:40:10  Show Profile  Reply with Quote
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 - 11/07/2013 :  09:53:10  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 11/07/2013 :  11:36:03  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 11/07/2013 :  11:38:48  Show Profile  Reply with Quote
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 - 11/07/2013 :  11:50:36  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 11/07/2013 :  12:03:13  Show Profile  Reply with Quote
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 - 11/07/2013 :  13:07:33  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 11/07/2013 :  13:10:37  Show Profile  Reply with Quote
yep..that you can dispense with

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000