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.