CODEWITHSUNDEEP
sqlpostgresqljoin
night mare
night mare

Reputation: 129

PostgreSQL: How to write query using joins?

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

Upvotes: 1

Views: 2213

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

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.

Upvotes: 1

Icarus
Icarus

Reputation: 63966

 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;

Upvotes: 1

Vincent Savard
Vincent Savard

Reputation: 35927

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.

Upvotes: 1

Evan Carroll
Evan Carroll

Reputation: 1

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;

Upvotes: 1

Related Questions