On Jan 17, 3:33 am, t...@audioboundary.com (Tom Molesworth) wrote:
> Hi Nick,
>
> On 17/01/12 00:18, Nick wrote:
>
>
>
>
>
>
>
>
>
> > I have three tables (users, books, pencils) and would like to get a
> > list of all users with a count and total price of their books and
> > pencils for 2012-01-01...
>
> > So with this data...
>
> > users (user_id)
> > 1
> > 2
> > 3
>
> > books (user_id, price, created)
> > 1 | $10 | 2012-01-01
> > 1 | $10 | 2012-01-01
> > 3 | $10 | 2012-01-01
>
> > pencils
> > 1 | $.50 | 2012-01-02
> > 3 | $.50 | 2012-01-01
> > 3 | $.50 | 2012-01-02
>
> > What is the most efficient way to get this result...
>
> > query_result (user_id, book_count, book_price_total, pencil_count,
> > pencil_price_total)
> > 1 | 2 | $20 | 0 | $0
> > 2 | 0 | $0 | 0 | $0
> > 3 | 1 | $10 | 1 | $.50
>
> Seems straightforward enough - left join the tables, group the result on
> user_id - so I'd write it as:
>
> select u.user_id, count(b.user_id) as "book_count",
> coalesce(sum(b.price), 0) as "book_price_total", count(p.user_id) as
> "pencil_count", coalesce(sum(b.price), 0) as "pencil_price_total"
> from users u
> left join books b on b.user_id = u.user_id and b.created = '2012-01-01'
> left join pencils p on p.user_id = u.user_id and p.created = '2012-01-01'
> group by u.user_id
> order by u.user_id
>
> If you need something more efficient, summary tables may help - hard to
> say without knowing more about the real data.
>
> Tom
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
Thanks Tom. Thats what I originally thought it would be, but my
results (disregarding the date clause) show that user 1 has 2 pencils
instead of 1, and user 3 has 2 books instead of 1.
I guess the LEFT JOIN is joining the first LEFT JOIN instead of the
users table.
Any other thoughts on how to get books and pencils to individually
LEFT JOIN the users table?