Re: Using AND in query

On 7 Aug 2010, at 23:18, Thomas Kellerer wrote:

> Or as an alternative:
>
> SELECT tid, purchase_date
> FROM orders
> WHERE item in ('Laptop', 'Desktop')
> GROUP BY tid, purchase_date
> HAVING count(*) = 2


This one is incorrect, it will also find people who bought two laptops or two desktops on the same date.

I was going to suggest:

SELECT tid, "date", 001::bit as type
  FROM orders
 WHERE item = 'Laptop'
UNION ALL
SELECT tid, "date", 010::bit as type
  FROM orders
 WHERE item = 'Desktop'
GROUP BY tid, "date"
HAVING type & 011::bit = 011::bit;

But I think David's solution is more readable, as it leaves the item names in tact.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4c5e6ee4286211665369939!