asked on

SQL Query

hi
i have rows in my db that present customer orders
there can be a lot of orders for each customer
i need to pull out only customer that have only close orders
for example :
CustName | CustNum | OrderNum | IsClosed |
xxx 1 100 1
xxx 1 101 0
yyy 2 105 1
yyy 2 110 1
ttt 3 333 0

after i run the query i will get CustNum
2
picos all the order are close for this customer
that is the query that i need

thanks ...

chaau

You can use the HAVING clause for this

SELECT CustNum
FROM orders
GROUP BY CustNum
HAVING MAX(IsClosed) = 1 and MIN(IsClosed) = 1

Open in new window

Tech_Men

ASKER

thanks for your quick answer
but i am getting this msg :
Operand data type bit is invalid for max operator.
IsClosed is a bit column

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Harshad M

Following would work (Not tested)

SELECT DISTINCT CustNum
FROM orders
where CustNum NOT IN
(SELECT CustNum FROM ORDERS WHERE IsClosed = 0)

Mark Ely

SELECT CustName, CustNum, IsClosed
FROM NameOfYourTable
WHERE IsClosed = 1
GROUP BY CustNum, CustName, IsClosed

Open in new window

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

SELECT o.CustName, o.CustNum, o.OrderNum, o.IsClosed --,o....
FROM orders o
INNER JOIN (
SELECT CustNum
FROM orders
GROUP BY CustNum
HAVING MIN(CAST(IsClosed AS tinyint)) = 1
) AS o_closed_only ON
o_closed_only.CustNum = o.CustNum

Tech_Men

ASKER

thanks
but only 2 of the answers are working
the best work better picos she didn't include null values also

thanks all