How would I write this query using joins? I have tried to do it but it doesn't work.
select fund.fundname, sum(shares) as tt, (sum(shares)*price.price) as value
from trans, fund, customer, price
where trans.fundid = fund.fundid
and trans.transdate = '2009-1-1'
and price.fundid = trans.fundid
and price.pricedate = trans.transdate
and trans.sin = customer.sin
and customer.name = 'Jacob'
group by fund.fundname, price.price;
Thanks
I suppose you're talking about explicit joins. As you have it, the joins are simply implicit. You'd do it like this:
SELECT fund.fundname, sum(shares) as tt, (sum(shares)*price.price) as value
FROM trans
JOIN fund
ON ( trans.fundid = fund.fundid )
JOIN customer
ON ( trans.sin = customer.sin )
JOIN price
ON ( price.fundid = trans.fundid )
WHERE trans.transdate = '2009-1-1'
AND price.pricedate = trans.transdate
AND customer.name = 'Jacob'
GROUP BY fund.fundname, price.price;
Postgresql also allows a nifty shorthand with USING which will only include one copy of the column in the end result set:
SELECT fundname, sum(shares) as tt, (sum(shares)*price.price) as value
FROM trans
JOIN fund
USING ( fundid )
JOIN customer
USING ( sin )
JOIN price
USING ( fundid )
WHERE trans.transdate = '2009-1-1'
AND price.pricedate = trans.transdate
AND customer.name = 'Jacob'
GROUP BY fund.fundname, price.price;
NATURAL JOINS will join on every like-named column.
You just have to separate the join conditions and the filter conditions :
SELECT fund.fundname, SUM(shares) AS tt, SUM(shares) * price.price AS value
FROM trans
INNER JOIN fund
USING (fundid)
INNER JOIN price
ON price.fundid = trans.fundid
AND price.pricedate = trans.transdate
INNER JOIN customer
USING (sin)
WHERE transdate = '2009-1-1'
AND customer.name = 'Jacob'
GROUP BY fund.fundname, price.price
Note that USING will "merge" the two columns, so you don't have to use aliases.
select fund.fundname, sum(shares) as tt, (sum(shares)*price.price) as value
from trans
join fund on fund.fundid=trans.fundid
join customer on customer.sin=trans.sin
join price on price.pricedate=trans.transdate
where
trans.transdate = '2009-1-1'
and customer.name = 'Jacob'
group by fund.fundname, price.price;
One more:
SELECT f.fundname
,sum(shares) AS tt
,(sum(shares) * p.price) AS value
FROM trans t
JOIN fund f USING (fundid)
JOIN price p ON (p.fundid, p.pricedate) = (t.fundid, t.transdate)
JOIN customer c USING (sin)
WHERE t.transdate = '2009-1-1'
AND c.name = 'Jacob'
GROUP BY 1, p.price;
I used some syntactical simplifications:
USING if left and right feature the same name. Carefull, if the name pops up multiple times, then the order of the JOINS make a difference or it may become ambiguous. Example here: sin. If there is a fund.sin as well, you have to use explicit JOIN ON instead.
(p.fundid, p.pricedate) = (t.fundid, t.transdate) is short for
(p.fundid = t.fundid AND p.pricedate = t.transdate)
GROUP BY 1 ist short for
GROUP BY <fist column in the select list>
More about joins in the fine manual here.
