CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Faiz Rahmathulla
Faiz Rahmathulla

Reputation: 19

SQL Server Select

I have the following table named tblMastTestElement

cTestCode    |  cElementCode
24HCRECLE    |  CRE
CALCRECLE    |  CRE
CALEXR       |  CRE
CRE          |  CRE
CRECLE       |  CRE
EGFR         |  CRE
EGFR         |  EG

I need a query to return EGFR as testcode which has the elements CRE And EG only

something like this

Select cTestCode
from tblMastTestElement
where cElementCode IN('CRE' And 'EG')

Upvotes: 2

Views: 105

Answers (4)

Bacon Bits
Bacon Bits

Reputation: 32155

This is nowhere near as efficient as Martin Smith's answer, but if you insist that doesn't work, we might as well be comprehensive in solutions:

SELECT cTestCode FROM tblMastTestElement WHERE cElementCode = 'CRE'
INTERSECT
SELECT cTestCode FROM tblMastTestElement WHERE cElementCode = 'EG'

If that doesn't work, run this:

SELECT cTestCode, cElementCode
FROM tblMastTestElement 
WHERE cTestCode = 'EGFR'
    AND cElementCode IN ('CRE','EG');

If that doesn't return two rows, then your data are bad. Note that that query isn't supposed to be an answer, it's just to prove out if your data are bad.

Upvotes: 1

jean
jean

Reputation: 4350

Select distinct t1.cTestCode
from tblMastTestElement t1
join tblMastTestElement t2 on t2.cTestCode = t1.cTestCode
where t1.cElementCode = 'EG'
and t2.cElementCode = 'CRE'

Upvotes: 2

Igor
Igor

Reputation: 62213

This would work.

Select t1.cTestCode
from tblMastTestElement t1
where t1.cElementCode = 'CRE'
AND EXISTS (
    Select 1
    from tblMastTestElement t2
    where t2.cElementCode = 'EG'
    AND t1.cTestCode = t2.cTestCode)
)

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453057

To return the cTestCode that have cElementCode for both 'CRE' and 'EG' (not either) you can use.

SELECT cTestCode
FROM   tblMastTestElement
WHERE  cElementCode IN ( 'CRE', 'EG' )
GROUP  BY cTestCode
HAVING COUNT(DISTINCT cElementCode) = 2

Upvotes: 4

Related Questions