LS

Tables: Customers, Orders, Order_Detail

How do I create a select statement that shows id_customer, id_order but only
when there is more then 1 orderdetail record?

Regards, Harry

Harry,

<< Tables: Customers, Orders, Order_Detail

How do I create a select statement that shows id_customer, id_order but only
when there is more then 1 orderdetail record? >>

A couple of alternatives:

1)

select
 c.CustomerID,
 o.OrderID
from
 Customers c
 join Orders o on o.CustomerID = c.CustomerID
 join Order_Detail od on od.OrderID = o.OrderID
group by c.CustomerID, o.OrderID
having count(od.OrderID) > 1

2)

select
 c.CustomerID,
 o.OrderID
from
 Customers c
 join Orders o on o.CustomerID = c.CustomerID
where o.OrderID in
(
 select OrderID
 from Order_Detail
 group by OrderID
 having count(OrderID) > 1
)

Ole Willy Tuv

Ole,

Thanks. I'll go for alternative 1 (seems to be a bit faster).

Regards, Harry


"Ole Willy Tuv" <owtuv@online.no> schreef in bericht
news:81B529EC-4ED4-44FB-81EC-E2F7A07AC69F@news.elevatesoft.com...
> Harry,
>
> << Tables: Customers, Orders, Order_Detail
>
> How do I create a select statement that shows id_customer, id_order but
only
> when there is more then 1 orderdetail record? >>
>
> A couple of alternatives:
>
> 1)
>
> select
>   c.CustomerID,
>   o.OrderID
> from
>   Customers c
>   join Orders o on o.CustomerID = c.CustomerID
>   join Order_Detail od on od.OrderID = o.OrderID
> group by c.CustomerID, o.OrderID
> having count(od.OrderID) > 1
>
> 2)
>
> select
>   c.CustomerID,
>   o.OrderID
> from
>   Customers c
>   join Orders o on o.CustomerID = c.CustomerID
> where o.OrderID in
> (
>   select OrderID
>   from Order_Detail
>   group by OrderID
>   having count(OrderID) > 1
> )
>
> Ole Willy Tuv
>
>