SQL query to return data from two separate rows in a table joined to a master table
- by Ali
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..