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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 subquery not as simple as I'd like

Author  Topic 

GrandVizier
Starting Member

14 Posts
Posted - 2007-07-10 : 21:25:21
Hej
I have here a nested query - it was simple and straight forward enough I thought, but damn it if I can't modify it to work - if I write the conditions all out it seems to work ok for me, but it doesn't look as sexy - any thoughts on how I can modify this (I've tried various techniques with EXISTS but they didn't seem to do the trick), of course if for some reason its more efficient to write it out, that would be better

SELECT FullName, travID 
FROM Traveler 
WHERE travTeamID = 6 AND 
     travID NOT IN  (SELECT rGuest1ID, rGuest2ID, rGuest3ID, rGuest4ID
       	     FROM Room  WHERE rTeamID=6)

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-10 : 21:43:49
If you are saying TravID NOT IN (...) your subquery should only return values of TravID. But your subquery is returning more than one column. Provide some sample data from each table and the expected output if you need help with the query.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

GrandVizier
Starting Member

14 Posts
Posted - 2007-07-10 : 22:09:47
sorry - i guess it wasn't as straight forward as i thought

CREATE TABLE [dbo].[Traveler](
	[travID] [int],
	[Fullname] [nvarchar](60),
	[travTeamID] [int] 
) 
INSERT  [dbo].[Traveler] ([travID],[Fullname],[travTeamID]) Values  (1,'John Smith', 6)
INSERT  [dbo].[Traveler] ([travID],[Fullname],[travTeamID]) Values  (2,'Joe Shmoe',6)
INSERT  [dbo].[Traveler] ([travID],[Fullname],[travTeamID]) Values  (3,'Bob Nubank',5)
INSERT  [dbo].[Traveler] ([travID],[Fullname],[travTeamID]) Values  (4,'Sally Sal',6)
INSERT  [dbo].[Traveler] ([travID],[Fullname],[travTeamID]) Values  (5,'Wonder Who',5)

CREATE TABLE [dbo].[Room](
	[rID] [int],
	[rTeamID] [int],
	[rGuest1ID] [int] NULL,
	[rGuest2ID] [int] NULL,
	[rGuest3ID] [int] NULL,
	[rGuest4ID] [int] NULL
) 
INSERT  [dbo].[Room] ([rID],[rTeamID],[rGuest1ID],[rGuest2ID],[rGuest3ID]) 
Values  (101, 6, 1,3,5)


this query is what I meant by writing it all out, it shows the desired results, but I imagine there has to be a better way to write it:
SELECT FullName, travID 
FROM Traveler 
WHERE travTeamID = 6 AND 
	travID NOT IN  (SELECT rGuest1ID
       	     FROM Room  WHERE rTeamID=6) AND
	travID NOT IN  (SELECT rGuest2ID
       	     FROM Room  WHERE rTeamID=6) AND 
	travID NOT IN  (SELECT rGuest3ID
       	     FROM Room  WHERE rTeamID=6) AND 
	travID NOT IN  (SELECT rGuest4ID
       	     FROM Room  WHERE rTeamID=6)


sorry - thats suppose to just be Room - I tried simplifying the tables by changing names and removing fields
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-07-10 : 22:13:15
Please also post the table structure for TeamRoom

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-07-10 : 22:21:01
1. You should normalize the Room table to something like this
CREATE TABLE [dbo].[Room]
(
	[rID] 		[int],
	[rTeamID] 	[int],
	[rGuestID] 	[int]
)


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-07-10 : 22:22:24
quote:
this query is what I meant by writing it all out, it shows the desired results

What is the desired result ? I tried your query and there is no record return. If i understand you correctly, It should return Joe & Sally ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-07-10 : 22:25:50
[code]SELECT FullName, travID
FROM Traveler t INNER JOIN Room r
ON t.travTeamID = r.rTeamID
WHERE t.travTeamID = 6
AND t.travID NOT IN
(
ISNULL(r.rGuest1ID, ''),
ISNULL(r.rGuest2ID, ''),
ISNULL(r.rGuest3ID, ''),
ISNULL(r.rGuest4ID, '')
)

/*
FullName travID
------------ -----------
Joe Shmoe 2
Sally Sal 4
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-07-10 : 22:29:23
This to show how easy and cleaner your code will be if your Room table is normalized


CREATE TABLE [dbo].[Traveler]
(
	[travID] [int],
	[FullName] [nvarchar](60),
	[travTeamID] [int] 
) 
INSERT  [dbo].[Traveler] ([travID],[FullName],[travTeamID]) VALUES  (1,'John Smith', 6)
INSERT  [dbo].[Traveler] ([travID],[FullName],[travTeamID]) VALUES  (2,'Joe Shmoe',6)
INSERT  [dbo].[Traveler] ([travID],[FullName],[travTeamID]) VALUES  (3,'Bob Nubank',5)
INSERT  [dbo].[Traveler] ([travID],[FullName],[travTeamID]) VALUES  (4,'Sally Sal',6)
INSERT  [dbo].[Traveler] ([travID],[FullName],[travTeamID]) VALUES  (5,'Wonder Who',5)

CREATE TABLE [dbo].[Room]
(
	[rID] [int],
	[rTeamID] [int],
	[rGuestID] [int],
) 
INSERT  [dbo].[Room] ([rID],[rTeamID],[rGuestID]) VALUES  (101, 6, 1)
INSERT  [dbo].[Room] ([rID],[rTeamID],[rGuestID]) VALUES  (101, 6, 3)
INSERT  [dbo].[Room] ([rID],[rTeamID],[rGuestID]) VALUES  (101, 6, 5)


SELECT	t.FullName, t.travID
FROM 	Traveler t left JOIN Room r
	ON	t.travTeamID	= r.rTeamID
	AND	t.travID	= r.rGuestID
WHERE	t.travTeamID 	= 6
AND	r.rGuestID	IS NULL

/*
FullName          travID      
------------ ----------- 
Joe Shmoe              2 
Sally Sal              4 
*/


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

GrandVizier
Starting Member

14 Posts
Posted - 2007-07-10 : 22:45:10
[code]Joe Shmoe 2
Sally Sal 4[/code]
this would be the desired result - but yeah, I tried the query again and this time it didn't return results either

and you're probably right about normalizing this khtan, but if each room has potentially 4 guests, how do you do this with the same Room id?
Go to Top of Page

GrandVizier
Starting Member

14 Posts
Posted - 2007-07-10 : 22:53:25
yeah - that is much cleaner - what I'll do is keep my Room table with all its other values, and then create a separate Guests table that has the rID & the rGuestID - that should do the trick - thanks
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site