CODEWITHSUNDEEP
mysqlsql
Mannu
Mannu

Reputation: 23

MySQL - Join two tables in a view, take rows from one and turn into columns

There is a Request Table and for each request, there is two guarantors required. So I made a table for request details and another table for guarantors details.

Now I need a view that included all requests and all guarantors name as I described below.

Could anyone please help me to resolve this..

I need MySQL Code to create this output. (for creating a PHP page)

These are my tables.

table tblReq 

|-------------------------------------------|  
| ReqID    | ReqAmount     | ReqDate        |
|-------------------------------------------|
| 1001     | 25000         | 2017-10-21     |
| 1002     | 21000         | 2017-10-10     |
| 1003     | 28000         | 2017-10-15     |
| 1004     | 20000         | 2017-10-22     |
|-------------------------------------------|

table tblGuarantors 

|------------------------------------------|  
| G_ID   | ReqID    | G_Name   | G_Contact |
|------------------------------------------|
| 10001  | 1001     | Ramu     | 123456    |
| 10002  | 1001     | Deepu    | 123456    |
| 10003  | 1002     | Mary     | 123456    |
| 10004  | 1002     | James    | 123456    |
| 10005  | 1003     | Muhad    | 123456    |
| 10006  | 1003     | Mannu    | 123456    |
|------------------------------------------|

Required Output

|------------------------------------------|
| ReqID | ReqAmount | G1_Name  | G2_Name   |
|------------------------------------------|
| 1001  | 25000     | Ramu     | Deepu     |
| 1002  | 21000     | Mary     | James     |
| 1003  | 28000     | Muhad    | Mannu     |
| 1004  | 20000     | Null     | Null      |
|------------------------------------------|

Can you anyone help me?

I tried this SQL, but it doesn't work as I assumed.

 SELECT 
     r.ReqID, 
     r.ReqAmount, 
     g1.G_Name, 
     g2.G_Name 
 from 
    (tblReq as r LEFT JOIN tblGuarantors as G1 on g1.ReqID = r.ReqID) 
 LEFT JOIN tblGuarantors as G2 on g2.ReqID = r.ReqID

A little help will be appreciated.

Thanks in advance....

Upvotes: 2

Views: 46

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use the following query:

SELECT r.ReqID, r.ReqAmount,
       MIN(G_Name) AS G1_Name,
       MAX(G_Name) AS G2_Name
FROM tblReq AS r
LEFT JOIN tblGuarantors AS g ON r.ReqID = g.ReqID
GROUP BY r.ReqID, r.ReqAmount

You have to amend the query in case of request records related to just one guarantor record.

Demo here

Edit:

To get all fields from guarantors table you need a different query:

SELECT r.ReqID, r.ReqAmount,
       g1.G_ID AS G1_ID, g1.G_Name AS G1_Name,
       g2.G_ID AS G2_ID, g2.G_Name AS G2_Name
FROM tblReq AS r
LEFT JOIN tblGuarantors AS g1 
   ON r.ReqID = g1.ReqID
LEFT JOIN tblGuarantors AS g2 
   ON r.ReqID = g2.ReqID 
WHERE (g1.G_ID < g2.G_ID) OR (g1.G_ID IS NULL)

The first LEFT JOIN gets the details of the guarantor having the smallest G_ID value, whereas the second LEFT_JOIN gets the details of the other guarantor. You also need predicate 

g1.G_ID IS NULL

so as to return request records with no guarantors as well.

Demo here

Upvotes: 1

R.Manjula
R.Manjula

Reputation: 11

Try this
 select tblReq.ReqID, tblReq.ReqAmount, MIN(tblGuarantors.G_Name) AS G1_Name, 
        MAX(tblGuarantors.G_Name) AS G2_Name 
 from tblGuarantors 
 left join tblReq on tblReq.ReqID=tblGuarantors.ReqID;

Upvotes: 0

Winteriscoming
Winteriscoming

Reputation: 191

I have written code in notepade, maybe you shoud be edit it, but i think it is will be worked. Sorry for my English.

SELECT 
    tblReq.ReqID, 
    tblReq.ReqAmount, 
    (SELECT G_Name FROM tblGuarantors WHERE tblGuarantors.ReqID = tblReq.ReqID ORDER by G_ID ASC LIMIT 1) as G1_Name,
    (SELECT G_Name FROM tblGuarantors WHERE tblGuarantors.ReqID = tblReq.ReqID ORDER by G_ID DESC LIMIT 1) as G2_Name 
FROM tblReq

Upvotes: 0

Related Questions