SQL Self Join Query Help

Question

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?

Answer 1

You can use aliases, which allow you to use two instances of the same table (replace MyTable with the name of your table):

SELECT A.clientid, A.EventNumber, B.City AS CityTo, A.EndDateTime AS MoveDateTime
  FROM MyTable A INNER JOIN MyTable B
       ON A.clientid = B.clientid
      AND A.EventNumber = B.EventNumber
      AND A.EndDateTime = B.StartDateTime
WHERE A.City = 'Chester'