SQL query to return data from two separate rows in a table joined to a master table

Question

I have a TWO tables of data with following fields

table1=(ITTAG,ITCODE,ITDESC,SUPcode)  
table2=(ACCODE,ACNAME,ROUTE,SALMAN)

This is my customer master table that contains my customer data such as customer code, customer name and so on...

Every Route has a supervisor (table1=supcode) and I need to know the supervisor name in my table which both supervisor name and code exist in one table.

table1 has contain all names separated by ITTAG. For example, supervisor's name has ITTAG='K'; also salesman's name has ITTAG='S'.

ITTAG    ITCODE   ITDESC         SUPCODE
------   ------   ------         -------
S        JT       JOHN TOMAS     TF
K        WK       VIKI KOO       NULL

Now this is the result which I want

ACCODE      ACNAME      ROUTE  SALEMANNAME      SUPERVISORNAME
-------     ------     ------  ------------     ---------------
IMC1010     ABC HOTEL   01     JOHN TOMAS       VIKI KOO

I hope this this information is sufficient to get the query..

Answer 1

Your data structure is either not clear or incomplete. It would help if you showed the actual example data for Table1 too, but there would be trouble.

 SELECT t2.ACCODE, t2.ACNAME, t2.ROUTE, a1.ITDESC AS Salesman, a2.ITDESC AS Supervisor
   FROM table1 AS t1
   JOIN table2 AS a1 ON t1.SALMAN = a1.ITCODE
   JOIN table2 AS a2 ON t1.?????? = a2.SUPCODE

It is not clear whether I've managed the join between Table1 and Table2 for the salesman information correctly; it is plausible, but the join for the supervisor should be similar, and yet there isn't a way to make that work. Hence the '??????' in the query.

The basic technique for joining twice to a single table is to cite it twice with different aliases, as shown. I usually use one letter or a letter and a digit for the aliases, as shown.