SQL many2many query

Question

I have 3 tables.

PersonT: id name

LotT: id price

PersonLotT: person_id lot_id

I want to know information like this: Michael (sum of all him lots, for example 300+400+500=1200) Alex 900 James 500

I tried to make different queries, but all was wrong.

Answer 1

Try this.

    select name, sum(price)
from PersonLotT PL join LotT L on PL.lot_id = L.id
join PersonT P on PL.person_id = P.id
group by name;

A sql fiddle is setup for exploring SQL Fidde