Hi,

I'm a bit of a database noob, so I was wondering if someone here could help. I can't post the actual problem I'm working on, but i hope the following hypothetical example will illustrate what I'm trying to do.

Take this table:
It shows how money has moved through a number of accounts. E.g. cash has gone from #4563 to #2276 and then on to other accounts. Some has gone back again. Whilst most accounts will be connected in this way, there will also be accounts that are unconnected to most the others.

What I want to be able do is pick an account (say #4563) and list all the accounts that link to it and be able to track backwards from any account to #4563. I'd also like to be able state how many steps separate an arbitrary account from #4563, but I suppose I can work that out easily enough when I track backwards.

I'm currently using programming loops to do this (which requires many database queries), but I wondered if it is possible to do it in one SQL query and if I should do so.

Sorry if this isn't very clear, I'm not the best at explaining things. I'll try to clarify it more if anyone asks.

you can do it with LEFT OUTER JOINs, one account link per join

which database are you planning to use?

Thanks for the reply. I'm currently planning on using sqlite.

sorry, not familiar with it, but i will venture a guess that it doesn't have recursive SQL

therefore you will have to code as many LEFT OUTER JOINs as necessary to cover the longest chain of links that you want to investigate