[FIXED] How do you join two fields in one table into a second table?

Issue

I have a table which contains three fields two of which link into the same table to lookup the value (the third links to a second table)

Deployment Table
---------------------------------
INDEX,  APP,  DEVSERVER, PRODSERVER (was previously just SERVER)

Server Table
---------------------------------
INDEX, NAME

App Table
---------------------------------
INDEX, NAME

Previously, when I had just SERVER in the deployment table I was doing:

SELECT d.index, a.name as `app`, s.name as `server`
FROM deployment as `d`, app as `a`, server as `s`
WHERE d.app = a.index AND d.server = s.index

and it was working fine. But now that I want to "double dip" into the server table with DEVSERVER and PRODSERVER I've not the foggiest way how to go about it.

Solution

First, you should use proper join syntax. Second, you need to introduce a second join to the server table:

SELECT d.index, a.name as app, dserv.name as devserver, pserv.name as prodserver
FROM deployment d join
     app a
     on d.app = a.index join
     server dserv
     on d.devserver = dserv.index join
     server pserv
     on d.prodserver = pserv.index;

Answered By - Gordon Linoff