i studying sql. there 1 particular query find difficult implement (using postgresql database).
could me that?
query : "who customers have @ least 1 order includes products @ least 3 product categories?".
the result should include 2 columns: customerid , quantity of orders.
the tables following:
- product
productid (id of product)|productcategoryid (product categories)both integers. - salesorderdetail :
salesorderid (id order)|salesorderdetailid|orderqty|productid, of them integers . - salesorderheader:
salesorderid|customerid, both integers.
the thing got far first part of statement:
select salesorderheader.customerid, salesorderdetail.orderqty salesorderheader, salesorderdetail; sample data:
productid | productcategoryid -----------+------------------- 1 | 2 2 | 2 3 | 3 4 | salesorderid | salesorderdetailid | orderqty | productid --------------+--------------------+----------+----------- 43659 | 1 | 1 | 776 43659 | 2 | 3 | 777 43659 | 3 | 1 | 778 salesorderid | customerid --------------+------------ 43659 | 29825 43660 | 29672 43661 | 29734
i see 2 approaches here:
1) generate subquery contains count salesorderid , count of distinct productcategories used salesorder.
select customerid, count(distinct oh.salesorderid) salesorderswithmorethan3categories salesorderheader oh inner join (select od.salesorderid, count(distinct p.productcategoryid) cnt salesorderdetail od inner join product p on p.proudctid = od.productid group od.salesorderid) b on oh.salesorderid = b.saleorderid , b.cnt >=3 group oh.customerid 2) use exists identify orderdetails count of distinct productcategories >=3
select oh.customerid, count(distinct oh.salesorderid) salesorderswithmorethan3categories salesorderheader oh exists (select 1 salesorderdetail od inner join product p on p.proudctid = od.productid oh.salesorderid = od.salesorderid group od.salesorderid having count(distinct p.productcategoryid) >=3) group oh.customerid 