> select order_id from
> (select o.order_id from orders o join order_lines ol using (order_id)
> where o.time > '2006-01-01T00:00' and o.time < '2007-01-01T00:00'
> and (ol.item_id = 10 or ol.item_id = 11 or ol.item_id = 12) group by
> o.order_id) as prints
> inner join
> (select ho.order_id from orders ho join order_lines hol using (order_id)
> where o.time > '2006-01-01T00:00' and o.time < '2007-01-01T00:00'
> and (ol.item_id = 20 or ol.item_id = 21 or ol.item_id = 22) group by
> o.order_id) as gifts
> using (order_id)
> except select order_id from
> (select ho.order_id from orders ho join order_lines hol using (order_id)
> where o.time > '2006-01-01T00:00' and o.time < '2007-01-01T00:00'
> and (ol.item_id = 30 or ol.item_id = 31 or ol.item_id = 32) group
> by o.order_id) as extra;
> When I do the 'select order_id' I get (after scrolling down):
> (1960 rows)
> But when I do a 'select count(order_id) I get:
> 2063
You did not show us exactly what you did, but if you simply blindly
replaced "select order_id" with "select count(order_id)" in the first
line above, then what you would have is
select count(order_id) from some-stuff
except
select order_id from some-other-stuff
so what you would get is either the count of some-stuff, or nothing
at all if any of the order_ids in some-other-stuff chanced to equal
the count. In any case it would not be the count of what the original
EXCEPT query returned, unless the EXCEPT wasn't eliminating any rows.
You need to wrap SELECT count(order_id) FROM ( ... ) around the entire
EXCEPT query to get what you want.
Thank you for your advise. It was the except-clause which gave me some
"headache". The query now looks like what you suggest:
select count(order_id)
(select order_id from
(select o.order_id from ... join orderlines ol using (order_id)
where ... as prints
inner join
(select o.order_id from ... join orderlines ol using (order_id)
where ... as extra
using (order_id)
except select order_id from
(select o.order_id from ... join orderlines ol using (order_id)
where ... as gifts)
as orders;
regards
Claus
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
