Hey guys!

I'm stuck with a pretty complex query. I thought you might be able to help

The table structure is very simple:

Travel(int UnitID, int LocationID, Date year)

What I'm trying to do is creating a directed graph from this data. Let's assume we have 10 different locations. And some discrete points of time. For every location and year, we should be able to tell, how many units move to a specific location next year; which is basically an edge...

That's the best thing I could come up with:

Code:
SELECT 
 count(X.UnitID) as Weight, X.Date, X.LocationID
FROM 
 Travel X, 
 (SELECT 
   UnitID 
  FROM 
   Travel 
  WHERE 
   Date=\"year_x\" AND LocationID="hardcoded_id") Y 
WHERE 
 Date=\"year_x+1\" AND X.UnitID=Y.UnitID
GROUP BY 
 Date, LocationID";
This can at least return the transition numbers from a specific location to all locations in the following year... given you provide it with the years and the location.

At the moment, I'm getting lists of locations and time with
Code:
select distinct LocationID from Travel
and
Code:
select distinct Date from Travel
But I don't really like the idea of nested loops and dozens of database calls. There should be a nicer way to get the data I need with only one call.

What I'd like to have is a view(year_x, year_x+1, location_x, location_x+1, weight) which I could use in any app to contruct my graph...

I appreciate any suggestion. I'd prefer "standard" SQL, but I can put the stuff on a DB2 as well