Select question - SQL Server Forums

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)

Select question

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-10-16 : 06:05:22

I have two tables:

The first contains possible user colour options

Table A

Colour
------
Red
Blue
Green
Black
Purple

the second table stores the previous user selections

Table B

User Colour
---- ------
1 Red
2 Red
1 Green
1 Black
3 Blue
3 Red

How can I return a list of colours that a user has not previously selected please?

For example, user 1 now only has choices of

Blue
Purple

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-16 : 06:24:14

Select Colour from Table_A where Colour Not IN (Select distinct Colour from Table_B)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-16 : 06:25:57

select
a.colour
from tableA as a
left join tableB as b on (b.Colour = a.Colour and b.User = @YourWantedUser)
where b.Colour is null

Webfred

Planning replaces chance by mistake

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-16 : 06:40:25

sorry forgot to add user in WHERE statement

SELECT [Colour]

FROM [dbo].[B]
where
Colour Not IN (
Select distinct Colour from dbo.A where [user] = '1'
)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-16 : 06:51:31

select
z.[user],z.colour
from
(select distinct t2.[user],t1.colour from [table b] t2 cross join [table a] t1)z
left join [table b] t3 on t3.[user]=z.[user] and t3.colour=z.colour
where t3.colour is null

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-10-16 : 07:25:23

Many thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources