how to join three tables

[blueguy777]

I am new for MySQL my tables structures are as follows

Table: registration
----------------------------------
| cardNo | mainAgentId |subAgentId|
----------------------------------
|  100   |       1     |     4    |
-----------------------------------
|  101   |       1     |     5    |
-----------------------------------
|  103   |       2     |     6    |
-----------------------------------
|  102   |       1     |     4    |
-----------------------------------
|  104   |       2     |     7    |
-----------------------------------

Table: mainsubagent

----------------------------------------------------
| mainAgentId | designation | firstName | lastName  |
----------------------------------------------------
|   1         | Main Agent  |  Akshay   | Kapoor    |
----------------------------------------------------
|   4         | Sub Agent   |  Sunil    | Jha       |
-----------------------------------------------------
|   5         | Sub Agent   |  Rajiv    | Shukla    |
-----------------------------------------------------
|   2         | Main Agent  |  Prakash  | Oswal     |
-----------------------------------------------------
|   6         | Sub Agent   |  Peter    | Alexander |
-----------------------------------------------------
|   7         | Sub Agent   |  Ali      | Asgar     |
-----------------------------------------------------

Table: payment
-----------------------------------
| cardNo | regName |installmentAmt |
-----------------------------------
|  101   | Pritam  |     600       |
-----------------------------------
|  103   | John    |     800       |
-----------------------------------
|  104   | Vicky   |     800       |
-----------------------------------
|  100   | Ramesh  |     600       |
-----------------------------------
|  102   | Rupesh  |     600       |
-----------------------------------



    // $agentid=1 it should search in mainAgentId or in subAgentId field of registration table

    SELECT p.cardNo, p.regName, p.installmentAmt FROM `payment` AS p INNER JOIN `registration` AS r ON p.cardNo=r.cardNo AND r.mainAgentId=$agentid OR p.cardNo=r.cardNo AND r.subAgentId=$agentid

-------------------------------
Current SQL query result
-----------------------------------
| cardNo | regName |installmentAmt |
-----------------------------------
|  100   | Ramesh  |     600       |
-----------------------------------
|  101   | Pritam  |     600       |
-----------------------------------
|  102   | Rupesh  |     600       |
-----------------------------------

How to join all three tables to get the following result (how to include mainAgentName & subAgentName columns?
-------------------------------------------------------------------
| cardNo | regName | mainAgentName | subAgentName | installmentAmt |  
-------------------------------------------------------------------
|  100   | Ramesh  | Akshay Kapoor |  Sunil Jha   |     600        | 
-------------------------------------------------------------------
|  101   | Pritam  | Akshay Kapoor | Rajiv Shukla |     600        |
-------------------------------------------------------------------
|  102   | Rupesh  | Akshay Kapoor |  Sunil Jha   |     600        |
-------------------------------------------------------------------

thanks in advance

[djjeavons]

Hi

The following SQL should work but tested in SQL Server, not MySQL.

    SELECT        derivedtbl_1.cardNo, derivedtbl_1.regName, dbo.mainsubagent.firstName + ' ' + dbo.mainsubagent.lastName AS mainAgentName, 
                             mainsubagent_1.firstName + ' ' + mainsubagent_1.lastName AS subAgentName, derivedtbl_1.installmentAmt
    FROM            (SELECT        p.cardNo, p.regName, p.installmentAmt, r.mainAgentId, r.subAgentId
                              FROM            dbo.payment AS p INNER JOIN
                                                        dbo.registration AS r ON p.cardNo = r.cardNo AND r.mainAgentId = 1 OR p.cardNo = r.cardNo AND r.subAgentId = 1) AS derivedtbl_1 INNER JOIN
                             dbo.mainsubagent ON derivedtbl_1.mainAgentId = dbo.mainsubagent.mainAgentId INNER JOIN
                             dbo.mainsubagent AS mainsubagent_1 ON derivedtbl_1.subAgentId = mainsubagent_1.mainAgentId

HTH

[blueguy777]

thanks djjeavons for reply but it's giving error message #1066 - Not unique table/alias: 'mainsubagent'

my code:

SELECT payment.cardNo, payment.regName, mainsubagent.firstName + ' ' + mainsubagent.lastName AS mainAgentName, mainsubagent.firstName + ' ' + mainsubagent.lastName AS subAgentName, payment.installmentAmount
FROM (SELECT p.cardNo, p.regName, p.installmentAmount, r.mainAgentId, r.subAgentId
FROM payment AS p INNER JOIN registration AS r ON p.cardNo = r.cardNo AND r.mainAgentId = 1 OR p.cardNo = r.cardNo AND r.subAgentId = 1) AS payment INNER JOIN
mainsubagent ON payment.mainAgentId = mainsubagent.mainAgentId INNER JOIN
mainsubagent AS mainsubagent ON payment.subAgentId = mainsubagent.mainAgentId

[djjeavons]

Hi

I don't believe you can create an alias that has the same name as your table (in this case mainsubagent), so change your last line (line 5) to use a different table name such as.

mainsubagent AS mainsubagent_Derived ON payment.subAgentId = mainsubagent_Derived.mainAgentId

[blueguy777]

current code and output

SELECT payment.cardNo, payment.regName, m.firstName +  ' ' + m.lastName AS mainAgentName, mainsubagent.firstName +  ' ' + mainsubagent.lastName AS subAgentName, payment.installmentAmount
FROM (

SELECT p.cardNo, p.regName, p.installmentAmount, r.mainAgentId, r.subAgentId
FROM payment AS p
INNER JOIN registration AS r ON p.cardNo = r.cardNo
AND r.mainAgentId =1
OR p.cardNo = r.cardNo
AND r.subAgentId =1
) AS payment
INNER JOIN mainsubagent AS m ON payment.mainAgentId = m.mainAgentId
INNER JOIN mainsubagent AS mainsubagent ON payment.subAgentId = mainsubagent.mainAgentId


//Current SQL query result
---------------------------------------------------------------------
| cardNo | regName |mainAgentName | subAgentName | installmentAmount |
---------------------------------------------------------------------
|  100   | Ramesh  |     0        |      0       |        600        |
---------------------------------------------------------------------
|  101   | Pritam  |     0        |      0       |        600        |
---------------------------------------------------------------------
|  102   | Rupesh  |     0        |      0       |        600        |
---------------------------------------------------------------------

[blueguy777]

modified code:

SELECT payment.cardNo, payment.regName, m1.firstName +  ' ' + m1.lastName AS mainAgentName, m2.firstName +  ' ' + m2.lastName AS subAgentName, payment.installmentAmount
FROM (

SELECT p.cardNo, p.regName, p.installmentAmount, r.mainAgentId, r.subAgentId
FROM payment AS p
INNER JOIN registration AS r ON p.cardNo = r.cardNo
AND r.mainAgentId =1
OR p.cardNo = r.cardNo
AND r.subAgentId =1
) AS payment
INNER JOIN mainsubagent AS m1 ON payment.mainAgentId = m1.mainAgentId
INNER JOIN mainsubagent AS m2 ON payment.subAgentId = m2.mainAgentId

Maybe you can try this statement:

select r.cardNo, p.regName, concat(m.firstname, ', ', m.lastname) 
 as "mainAgentName", concat(s.firstname, ', ', s.lastname) 
  as "subAgentName", p.installmentAmt 
   from registration r join payment p on r.cardNo = p.cardNo
    join mainsubagent m on r.mainAgentId = m.mainAgentId
     join registration s on s.subAgentId = m.mainAgentID;

Unfortunately, I didnt test it on a real database so far.

1STDAN