SQL Self Join Query Help

Posted by hdoe123 on Stack Overflow See other posts from Stack Overflow or by hdoe123
Published on 2010-04-17T10:12:27Z Indexed on 2010/04/17 10:23 UTC
Read the original article Hit count: 392

Hi All,

I'm trying to work out a query that self join itself on a table using the eventnumber. I've never done a self join before.

What i'm trying to query is when a client has started off in a city which is chester to see what city they moved to. But I dont want to be able to see if they started off in another city. I would also like be only see the move once (So i'd only like to see if they went from chester to london rather then chester to london to wales)

The StartTimeDate is the same EndDateTime if they moved to another city.

Data example as follows if they started off in the city chester :-

 
clientid      EventNumber       City             StartDateTime                  EndDateTime
1                  1            Chester          10/03/2009               11/04/2010 13:00
1                  1            Liverpool        11/04/2010 13:00         30/06/2010 16:00 
1                  1            Wales            30/07/2010 16:00

the result I would like to see is on the 2nd row - so it only shows me liverpool.

Could anyone point in the right direcetion please?

© Stack Overflow or respective owner

Related posts about sql

Related posts about tsql