Querying multiple Rows? - 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

General SQL Server Forums

New to SQL Server Programming

Querying multiple Rows?

Author

Topic

Jnetik
Starting Member

5 Posts

Posted - 2011-12-21 : 06:22:53

I'm unsure exactly what I'm trying to do, but I know what I want.

I have a table like the following:

SecurityID CodeID Code
123 1 ABC
123 2 DEF
156 1 GHI
134 1 JKL
134 2 MNO

I want to search for all records that have a CodeID of 1 but DO NOT have a CodeID 2, if this is the case then I want to return the Code.

So the above case would yield

SecurityID Code
156 GHI

because it is the only unique SecurityID that does not have a CodeID = 2

Thanks for any help

bamabala
Starting Member

11 Posts

Posted - 2011-12-21 : 06:37:20

declare @t table (SecurityID int, CodeID int,Code varchar(20))
insert @t select 123,1,'ABC'
insert @t select 123,2,'DEF'
insert @t select 156,1,'GHI'
insert @t select 134,1,'JKL'
insert @t select 134,2,'MNO'
declare @CodeID int = 1
SELECT
*
FROM
@t
WHERE
CodeID = @CodeID
AND SecurityID NOT IN (SELECT SecurityID FROM @t WHERE CodeID <>@CodeID)

I think this will work. try this.

Jnetik
Starting Member

5 Posts

Posted - 2011-12-21 : 06:47:32

I'm not sure I'll be able to do this,

my real table has about 18,000 SecurityID entries in it so I can't write a line of code for each one.

Also I do not have permission to write data, only to query. Or would declare @t table (SecurityID int, CodeID int,Code varchar(20)) create a temporary table?

bamabala
Starting Member

11 Posts

Posted - 2011-12-21 : 07:42:13

i created temp table for ur understanding u can use ur real table in the place of temp table and can i know are u paasing codeid as input or not.

mokru
Starting Member

4 Posts

Posted - 2011-12-21 : 17:03:10

Not sure I really understand, but I think I do. How about:

WITH NoDupes as
(
Select SecurityID, CodeID
From T (your table name)
Group By SecurityID, CodeID
Having Count(*) = 1
)
Select *
From T (your table name)
inner join nodupes on
(
t.securityID = nodupes.securityID
and
t.CodeID = nodupes.CodeID
and
t.Code = nodupes.Code
)

Could you restate the problem if this is not what you are after?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources