Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    2

    Unanswered: Advanced queries

    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:
    Code:
    fromAccountNo  |  toAccountNo
             4563  |         2276
             2276  |         2666
             2276  |         3798
             2276  |         2100
             2276  |         4563
             2666  |         1249
    ...
    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can do it with LEFT OUTER JOINs, one account link per join

    which database are you planning to use?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    Posts
    2
    Thanks for the reply. I'm currently planning on using sqlite.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •