SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Create loop within SQL SELECT statement until chain broken.

    Hypothetical database for events happening around the world.

    EVENT
    event_id | event_name
    1 | Great Wall Party
    2 | Times Square Dance
    3 | Sydney Blowout

    PLACE
    place_id | place_name
    54 | Times Square
    55 | Manhattan
    56 | New York City
    57 | New York State
    58 | USA

    EVENTPLACE
    eventid | placeid
    2 | 54

    RELATEDPLACES
    rel_placeid1 | rel_placeid2
    54 | 55
    55 | 56
    56 | 57
    57 | 58

    If I display the event, Times Square Dance, Id like to display all the places that appear up the chain of its associated places via the RELATEDPLACES table (i.e. Times Square, Manhattan, New York City, New York State, USA). Likewise, if I call all events for USA, Id like the Times Square dance to be listed, given its EVENTPLACE (Times Square) appears at the bottom of the RELATEDPLACES chain of associations starting with USA.

    I think I need to create an inner loop within my SQL command so that it keeps performing until there is a break in the chain. So far (using the first of the two above examples) I have:-

    Code:
    SELECT place_nm FROM eventplace
    INNER JOIN relatedplaces ON placeid = rel_placeid1
    INNER JOIN place ON rel_placeid2 = place_id
    [**where the loop should begin:
    INNER JOIN relatedplaces ON place_id = rel_placeid1
    INNER JOIN place ON rel_placeid2 = place_id
    end loop**]
    WHERE eventid = 2;
    This is complicated by the fact that I need different table aliases for each loop, which means I cant state in the opening SELECT statement that I want to be collecting all the place_name data in the same column.

    Im not sure if I what I am trying to achieve is even possible and my current fallback solution is to list all of Times Squares related places in the RELATEDPLACES table, rather than just the next largest place (Manhattan), but this seemed like the better solution (and would also save database space).

    Can anyone suggest the SQL SELECT command I might need to use? Cheers!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    there are no loops in sql

    that said, you did not mention which database you're using, so let me suggest you search for recursive CTE, with the proviso that your database might not support it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy - I did not think there would necessarily be a simple answer on this one. Will look into your suggestion, which is new territory for me (I always feel a little humbled in your presence).


Tags for this Thread

Bookmarks

Posting Permissions

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