Mysql – selecting table1 and table2 with single data row and table 3 with multiple data rows

MySQL

I want to retrieve data from table1(payments), table2(properties), and table3 (property.assessments). See my visual representation below:

tables screenshot

Snippets:

SELECT a.payment_id, a.payment_arp_number, a.payment_pin, b.property_id, b.property_arp_number,
    b.property_location, c.property_id, group_concat(c.pa_classification separator ', ') 
    as pa_classification, sum(c.pa_market_value) as pa_market_value
FROM `property.payments` a, properties b, `property.assessments` c
WHERE a.payment_arp_number = b.property_arp_number
AND b.property_id = c.property_id

The problem with this is it only fetch one row, if not it will fetch all but the classification and market_value are just the same with other rows. I want something like the expected result.

Expected result:
property.assessments can have multiple row of classification and market value with the same property_id.

result screenshots

Best Answer

The answer to my question is, all I need to do is add the GROUP BY.

SELECT a.payment_id, a.payment_arp_number, a.payment_pin, b.property_id, b.property_arp_number,
b.property_location, c.property_id, group_concat(c.pa_classification separator ', ') 
as pa_classification, sum(c.pa_market_value) as pa_market_value
FROM `property.payments` a, properties b, `property.assessments` c
WHERE a.payment_arp_number = b.property_arp_number
AND b.property_id = c.property_id
GROUP BY a.payment_id
Related Question