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

Question

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

• Jehad Keriaki: I think you should add the "GROUP BY" part to your statement.
• rpm07: it's funny that I forgot that. that solved my problem :)

Answer 1

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