CODEWITHSUNDEEP
sqlsubquery
user2073957
user2073957

Reputation: 1

SQL using multiple values in a subquery in the same table

I have one table that has multiple rows with multiple event types.

Every start event has an end event. Due to a design choice, the start event has the end event's ID in a column, but not the other way around. Similarly, the start reason is not the same as the end reason.

My columns are:

eventTYPE - the type of event (start, end, terminate etc)
eventID - unique ID
eventDate - date the event will happen
endEvent - ID of end event (if event type <> start - this will be NULL)
reason - Reason for the event

I need to return all the eventType=END that's comparitive eventType=START's reason is not of a certain reason on a user specified date.

I know I need to use the following three statements, but I am not sure how to combine them.

SELECT eventId 
FROM Events
WHERE eventType='END' and eventDate='<USER SPECIFIED>'

SELECT endEvent    
FROM Events
WHERE eventType='START' and reason <> 'mgp' and endEvent='<ID FROM ABOVE>'

SELECT * 
FROM dbo.Events 
WHERE eventType='END' and eventId='<END EVENT FROM ABOVE>'

Any help is appreciated!

Upvotes: 0

Views: 343

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

The query is something like this:

select *
from events se left outer join
     events ee
     on se.endevent = ee.eventid and se.eventtype = 'start' and ee.eventtype = 'end'
where ee.eventdate = '<USER SPECIFIED>' and
       se.reason <> 'mgp'

This just joins the events together, matching the start and end record, and then applies the logic to that result.

Upvotes: 2

john
john

Reputation: 581

How about,

SELECT * 
FROM dbo.Events 
WHERE eventType='END' and eventId IN (

   SELECT endEvent    
   FROM Events
   WHERE eventType='START' and reason <> 'mgp' and endEvent IN (

      SELECT eventId 
      FROM Events
      WHERE eventType='END' and eventDate='<USER SPECIFIED>'

   )
)

Upvotes: 0

Related Questions