Select single row from table with multiple row values from different table

Question

I have 2 MySQL tables, I want to select 1 row for each record in TableA but each record can have up to 2 "Payer" records from TableB so I want each value from Table B as a single row.

Here is a small example of the tables I have:

TableA 
RecordID |      Name |         Date |
       1 | 'Record1' | '2014-01-01' |
       2 | 'Record2' | '2014-01-02' |

TableB 
RecordID | AmountPaid | PayerCount | PayerCode |
       1 |     $10.99 |          1 |      1234 |
       1 |     $15.99 |          2 |      9876 | 
       2 |     $27.99 |          1 |      1234 | 
       2 |     $61.99 |          2 |      9876 |

TableB.PayerCount: 1=First Payer and 2=Second Payer.

This is the info that I want from these tables:

ID |      Name |         Date | FirstPayerCode | FirstPayerAmount | SecondPayerCode | SecondPayerAmount |
 1 | 'Record1' | '2014-01-01' |           1234 |           $10.99 |            9876 |            $15.99 |
 2 | 'Record2' | '2014-01-02' |           1234 |           $27.99 |            9876 |            $61.99 |

I just cant seem to find anything that will give me these results.

Any help would be greatly appreciated. Thanks.

Right now I am just getting 2 rows for each record with "SELECT * FROM TableA JOIN TableB USING (RecordID)". But I need them in one row for each record id, but can't think of (or find) the syntax for what I need. — Brian Tacker, Mar 27 '14 at 19:19

score 2 · Accepted Answer · answered Mar 27 '14 at 19:22

SELECT      tableA.id, tableA.name, tableA.date, 
            FirstPlayerCode, FirstPlayerAmount, 
            SecondPlayerCode, SecondPlayerAmount 
FROM        tableA
LEFT JOIN   (SELECT PayerCode AS FirstPlayerCode, AmountPaid AS FirstPlayerAmount 
             FROM   tableB
             WHERE  PlayerCount = 1) p1 ON p1.RecordID = tableA.RecordID 
LEFT JOIN   (SELECT PayerCode AS SecondPlayerCode, AmountPaid AS SecondPlayerAmount 
             FROM   tableB
             WHERE  PlayerCount = 2) p2 ON p2.RecordID = tableA.RecordID

Beautiful, that's exactly what I needed. I wasn't aware you could join on a select statement. Thanks for the help! — Brian Tacker, Mar 27 '14 at 19:43

Select single row from table with multiple row values from different table

1 Answers1

Linked