INNER JOINS on the same table using 2 values


I have a database which includes the following tables:

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

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

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.


use table aliases…

SELECT links.*
     , linktypes.linktypename
     , [COLOR="#FF0000"]site_a[/COLOR].sitename AS site_a_name
     , [COLOR="#0000FF"]site_b[/COLOR].sitename AS site_b_name
  FROM links
  JOIN linktypes
    ON linktypes.linktypeid = links.linktypeid
  JOIN sites [COLOR="#FF0000"][/COLOR][COLOR="#FF0000"]AS site_a[/COLOR]
    ON [COLOR="#FF0000"]site_a[/COLOR].siteid = links.linksitea
  JOIN sites [COLOR="#0000FF"]AS site_b[/COLOR]
    ON [COLOR="#0000FF"]site_b[/COLOR].siteid = links.linksiteb
 WHERE 'SITE101' IN (links.linksitea,links.linksiteb)

That works great thanks! I hadn’t thought of using WHERE xxx IN.