Search for records where there is only one 1

[ckeener]

Hello,

I am trying to figure out how to write a query that would show me where there is only one record that matches a certain criteria.

The records are session information and there should be a 'Begin' and an 'End' record. There is an ID that consists of a begintime and a connectionID but it is the same for both the 'Begin' and 'End' records.

How would I find records that only have a 'Begin' row and not an 'End' row?

Hope this question makes sense!!!

Thanks,
ckeener

 

[PHV]

One way:
SELECT *
FROM yourTable
WHERE ID In (Select ID From yourTable Group By ID Having Count(*)=1)

Another way:
SELECT A.*
FROM yourTable A INNER JOIN (
SELECT ID FROM yourTable GROUP BY ID HAVING Count(*)=1
) B ON A.ID = B.ID

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

 

[ckeener]

Thank you PH,

I was sure that it could not be too complicated and you showed the easiest way of getting what I needed. It makes perfect sense now.

Thanks again,
ckeener