SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question INNER JOINS on the same table using 2 values

    Hello.

    I have a database which includes the following tables:

    SITES
    siteid e.g. SITE101, SITE102, SITE103, SITE104, SITE105
    sitename e.g. London, Paris, New York, Washington, Sydney

    LINKS
    linkid [Auto Incrementing number]
    linksitea e.g. SITE101, SITE101, SITE105, SITE104
    linksiteb e.g. SITE102, SITE103, SITE101, SITE103
    linktypeid e.g. 1, 3, 2, 1

    LINKTYPES
    linktypeid e.g. [Auto Incrementing number]
    linktypename e.g. 10MB VPN, 6MB VPN, 4MB VPN

    It basically lists a load of sites around the world, and the circuits between the sites.

    On each site, I am doing a query on the LINKS table which lists the links from and to the site. I.e. if I am viewing the site info for SITE101, then I need to check both linksitea and linksiteb for the siteid (as it depends on which way round the siteid's have been entered), and am using the following:
    SELECT * FROM links WHERE linksitea = 'SITE101' OR linksiteb = 'SITE101'
    This would return 3 records.

    I then did an INNER JOIN with the LINKTYPES table to also display the linktypename:
    SELECT * FROM links INNER JOIN linktypes ON links.linktypeid = linktypes.linktypeid WHERE linksitea = 'SITE101' OR linksiteb = 'SITE101'
    This also worked fine.

    What I am struggling with is to then do an inner join to get the sitenames as well. I need to do 2 inner joins between links.linksitea and links.linksiteb against sites.siteid. I can do it with the sitea end:
    SELECT links.*, sites.sitename AS site1name FROM links INNER JOIN linktypes ON links.linktypeid = linktypes.linktypeid INNER JOIN sites ON links.linksitea = sites.siteid WHERE linksitea = 'SITE101' OR linksiteb = 'SITE101'
    I need to repeat the bit in bold/underlined somehow for linksiteb.........

    Anyone able to point me in the right direction please??

    Many thanks.

    Andrew.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    use table aliases...
    Code:
    SELECT links.*
         , linktypes.linktypename
         , site_a.sitename AS site_a_name
         , site_b.sitename AS site_b_name
      FROM links 
    INNER 
      JOIN linktypes 
        ON linktypes.linktypeid = links.linktypeid 
    INNER 
      JOIN sites AS site_a
        ON site_a.siteid = links.linksitea
    INNER 
      JOIN sites AS site_b
        ON site_b.siteid = links.linksiteb
     WHERE 'SITE101' IN (links.linksitea,links.linksiteb)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That works great thanks! I hadn't thought of using WHERE xxx IN.

    Thanks.
    Andrew.


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
  •