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