Need help with query 1

[DougP]

Need to find only projects [Project Number] where Trackingcode = 24 (project started) but if that project number also has a Trackingcode =49 (project completed) don't show it.

sample data
Project Number TrackingCode
060399 24
060399 49
060232 24
060888 24
060888 49

So I want to see only 060232 on the list
060399 and 060888 both have a TrackingCode of 49

TIA

DougP, MCP, A+

 

[SkipVought]

Hi,

How about...

Select *
From TheTable
where Trackingcode = 24

assuming that Trackingcode is defined in your table as a NUMERIC data type. Otherwise you need TICS delimiting the literal.

Skip,

_[red][/red]

 

[PHV]

One way:
SELECT [Project Number]
FROM yourTable A
WHERE Trackingcode = 24
AND NOT EXISTS (SELECT * FROM yourTable WHERE Trackingcode = 49 AND [Project Number] = A.[Project Number])

Another way:
SELECT A.[Project Number]
FROM yourTable A LEFT JOIN (
SELECT [Project Number] FROM yourTable WHERE Trackingcode = 49
) B ON A.[Project Number] = B.[Project Number]
WHERE A.Trackingcode = 24 AND B.[Project Number] IS NULL



Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DougP]

Thanks PH
I'll give it a try next week, Im off 'till Jan 2

Skip, I tried that and it only returns those with 24 but ignores if they have a 49 or not.


DougP, MCP, A+

 

[DougP]

PH, It works perfect,
have a star

DougP, MCP, A+