|
Author |
Message |
feef |
This post is not being displayed .
|
feef Energiser Bunny
Joined: 11 Feb 2002 Karma :
|
Posted: 16:00 - 23 Feb 2006 Post subject: simple SQL query is giving me a headache! (help ;) ) |
|
|
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 |
|
Back to top |
|
You must be logged in to rate posts |
|
|
veeeffarr |
This post is not being displayed .
|
veeeffarr Super Spammer
Joined: 22 Jul 2004 Karma :
|
|
Back to top |
|
You must be logged in to rate posts |
|
|
Kickstart |
This post is not being displayed .
|
Kickstart The Oracle
Joined: 04 Feb 2002 Karma :
|
|
Back to top |
|
You must be logged in to rate posts |
|
|
feef |
This post is not being displayed .
|
feef Energiser Bunny
Joined: 11 Feb 2002 Karma :
|
|
Back to top |
|
You must be logged in to rate posts |
|
|
feef |
This post is not being displayed .
|
feef Energiser Bunny
Joined: 11 Feb 2002 Karma :
|
|
Back to top |
|
You must be logged in to rate posts |
|
|
veeeffarr |
This post is not being displayed .
|
veeeffarr Super Spammer
Joined: 22 Jul 2004 Karma :
|
|
Back to top |
|
You must be logged in to rate posts |
|
|
Kickstart |
This post is not being displayed .
|
Kickstart The Oracle
Joined: 04 Feb 2002 Karma :
|
|
Back to top |
|
You must be logged in to rate posts |
|
|
Kickstart |
This post is not being displayed .
|
Kickstart The Oracle
Joined: 04 Feb 2002 Karma :
|
Posted: 16:23 - 23 Feb 2006 Post subject: |
|
|
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 |
|
Back to top |
|
You must be logged in to rate posts |
|
|
feef |
This post is not being displayed .
|
feef Energiser Bunny
Joined: 11 Feb 2002 Karma :
|
|
Back to top |
|
You must be logged in to rate posts |
|
|
Kickstart |
This post is not being displayed .
|
Kickstart The Oracle
Joined: 04 Feb 2002 Karma :
|
|
Back to top |
|
You must be logged in to rate posts |
|
|
veeeffarr |
This post is not being displayed .
|
veeeffarr Super Spammer
Joined: 22 Jul 2004 Karma :
|
Posted: 16:32 - 23 Feb 2006 Post subject: |
|
|
I'd use the subselect unless speed really is an issue.
Unless you really want to be arsed with joins |
|
Back to top |
|
You must be logged in to rate posts |
|
|
feef |
This post is not being displayed .
|
feef Energiser Bunny
Joined: 11 Feb 2002 Karma :
|
|
Back to top |
|
You must be logged in to rate posts |
|
|
Kickstart |
This post is not being displayed .
|
Kickstart The Oracle
Joined: 04 Feb 2002 Karma :
|
|
Back to top |
|
You must be logged in to rate posts |
|
|
feef |
This post is not being displayed .
|
feef Energiser Bunny
Joined: 11 Feb 2002 Karma :
|
|
Back to top |
|
You must be logged in to rate posts |
|
|
Kickstart |
This post is not being displayed .
|
Kickstart The Oracle
Joined: 04 Feb 2002 Karma :
|
|
Back to top |
|
You must be logged in to rate posts |
|
|
feef |
This post is not being displayed .
|
feef Energiser Bunny
Joined: 11 Feb 2002 Karma :
|
|
Back to top |
|
You must be logged in to rate posts |
|
|
Old Thread Alert!
The last post was made 18 years, 109 days ago. Instead of replying here, would creating a new thread be more useful? |
|
|
|