simple SQL query is giving me a headache! (help ;) )

[feef]

this shoud be simple, but my brain's hurting.

I have a table with 3 fields...

IDnum,content,application

eg
123,stop,fb
123,start,fb
123,continue,fb
123,fault,fb
123,start,gt
123,stop,gt
123,continue,gt
123,start,ha
321,stop,fb
321,start,fb
321,continue,fb
321,fault,fb
321,start,gt


I need to know All those unique IDs that have "start" against them, but have not got "stop" against them, I'm not interested in the other content strings.

I need to query it based on a specific application ID.

there will be many records from each IDnum, but only one start for each. I need to know all those IDnums that have "started" and not "stopped"

this is on SQL Server.

tia

a
____________________
Mudskipper wrote: feef, that is such a beautiful post that it gave me a lady tingle
Windchill calculator - London Bike parking
Blog and stuff - PlentyMoreFish dating

[veeeffarr]

Is IDNum Primary?

That list is an example of the values in your table?

So basically you want to know how many have started?

[Kickstart]

select distinct idnum
from blah
where content = "start"
and idnum not in (select idnum from blah where content = "stop")

Quick and nasty way of getting a list.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing

[feef]

[feef]

[veeeffarr]

Sorry mate,

I didn't read properly, Keith's should work.

[Kickstart]

Hi

Not sure if you could play around with a left outer join, joining the table to itself on the idnum with one having start, the other stop, and then ignore the ones where it does find a match.

Failing that you could make the subselect more specific.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing

[Kickstart]

Hi

Not tried this, but something like it might work (too many versions of SQL, think this works in some but not something I have used)

select *
from (select a.idnum subIdnum, a.content subContentA, b.content subContentB
from blah a left outer join blah b
on a.idnum = b.idnum
where a.content = "start"
and b.content = "stopped")
where subContentB is null

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing

[feef]

[Kickstart]

Hi

Try the example. Be interested to know if it works (suspect it would in Oracle).

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing

[veeeffarr]

I'd use the subselect unless speed really is an issue.

Unless you really want to be arsed with joins

[feef]

[Kickstart]

Hi

Have you tried the join example?

Just interested in whether it would work.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing

[feef]

[Kickstart]

Hi

Interesting, something Oracle manages to do!

If the SQL is needed for a program then at least it is useable.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing

[feef]