Count result of many joins in postgresql

Question

I have this query:

    select count(*) 
            from seller s 
            left join product pr on s.id = pr.seller_id 
            left join special_price_discount spd on pr.id = spd.product_id 
            left join product___tag pt on pr.id = pt.product_id 
            left join tag t on pt.tag_id = t.id 
            left join product___size ps on ps.product_id = pr.id

In db I have two products, how I get right count result, because now I get 19 instead of 2.

I tried add 'group by pr.id', but then query returns 2 results, with 'distinct' I also have 19. Or there are too many joins and I cannot gain appropriate result? Any ideas?

What is your target? The number of products? Or the number of products that fulfill a certain condition? Can you phrase it? — gregor, Jun 16 '13 at 14:39
I want to get the number of products return by above query, 2 in this case. — Piotr Kozlowski, Jun 16 '13 at 14:41
This query may not be doing what you'd expect it to do, because of indirect cross joins or "proxy cross joins" as I like to call them. Consider [this related question](http://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result). — Erwin Brandstetter, Jun 16 '13 at 16:29

score 1 · Answer 1 · answered Jun 16 '13 at 14:57

1

You could do e.g. select count(distinct product.*).

Or more simply, if counting the products is what you really want:

select count(*) from products;

answered Jun 16 '13 at 14:57

Denis de Bernardy

75,850
13
131
154

I have to consider WHERE clause where are some conditions related with joins. – Piotr Kozlowski Jun 16 '13 at 15:44

Count result of many joins in postgresql

1 Answers1