Help with select statement

Hi guys,

I have the following data

CaseNumber ConnectionToCase PersonID

-

00001 A 500

00001 J 235

00001 6 014

00001 K 016

00002 A 500

00002 B 477

00002 6 251

00002 L 355

00003 F 577

00003 J 235

00003 C 744

00003 K 563

00005 A 501

00005 K 455

00009 R 500

00009 6 017

00009 K 011

I would like to select all columns based on casenumber that contains only ConnectionToCase = '6'.

Therefore i need a query that will return this result:

CaseNumber ConnectionToCase PersonID

00001 A 500

00001 J 235

00001 6 014

00001 K 016

00002 A 500

00002 B 477

00002 6 251

00009 R 500

00009 6 017

00009 K 011

How can i do that? thanks guys

Jul.

Something like this should work:

Code Snippet

SELECT
CaseNumber,
ConnectionToCase,
PersonID
FROM MyTable
WHERE CaseNumber IN ( SELECT CaseNumber
FROM MyTable
WHERE ConnectionToCase = '6'
)

|||

See if this works for you:

Code Snippet

select a.CaseNumber, a.ConnectionToCase, a.PersonID

from casedata a

inner join casedata b

on a.CaseNumber = b.CaseNumber

and b.ConnectionToCase = '6'

|||

Here you go....

Code Snippet

Create Table #casedata (

[CaseNumber] Varchar(100) ,

[ConnectionToCase] Varchar(100) ,

[PersonID] Varchar(100)

);

Insert Into #casedata Values('00001','A','500');

Insert Into #casedata Values('00001','J','235');

Insert Into #casedata Values('00001','6','014');

Insert Into #casedata Values('00001','K','016');

Insert Into #casedata Values('00002','A','500');

Insert Into #casedata Values('00002','B','477');

Insert Into #casedata Values('00002','6','251');

Insert Into #casedata Values('00002','L','355');

Insert Into #casedata Values('00003','F','577');

Insert Into #casedata Values('00003','J','235');

Insert Into #casedata Values('00003','C','744');

Insert Into #casedata Values('00003','K','563');

Insert Into #casedata Values('00005','A','501');

Insert Into #casedata Values('00005','K','455');

Insert Into #casedata Values('00009','R','500');

Insert Into #casedata Values('00009','6','017');

Insert Into #casedata Values('00009','K','011');

Select

*

From

#casedata

where

[CaseNumber] in (

Select

[CaseNumber]

From

#casedata

Where [ConnectionToCase]='6'

)

|||

SELECT * FROM myTable

WHERE CaseNumber IN (SELECT CaseNumber FROM myTable WHERE ConnectionToCase = 6)

Adamus

|||

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I've got this error when ran this query:

SELECT
CaseNumber,
ConnectionToCase,
PersonID
FROM MyTable
WHERE CaseNumber = ( SELECT CaseNumber
FROM MyTable
WHERE ConnectionToCase = '6'
)

|||Thanks guys!!