This sounds really easy but I'm stumped!
Order entry app using 2 tables: OE_HEADER and OE_LINE

select h.ord_no, l.status
from OE_HEADER h inner join OE_LINE l
on h.ord_no = l.ord_no
where l.status = 'Y'

I would like to return an ord_no when ALL the line items have a status = 'Y'
The default value for status is NULL

So if an order has 5 line items, 4 have a status ='Y' but 1 has status =
NULL, I don't want to return that order.
Use Distinct?

Thanks

Artie wrote:

> This sounds really easy but I'm stumped!
> Order entry app using 2 tables: OE_HEADER and OE_LINE
>
> select h.ord_no, l.status
> from OE_HEADER h inner join OE_LINE l
> on h.ord_no = l.ord_no
> where l.status = 'Y'

(Side note: I recognize the app; the join should also include ord_type)

> I would like to return an ord_no when ALL the line items have a status = 'Y'
> The default value for status is NULL
>
> So if an order has 5 line items, 4 have a status ='Y' but 1 has status =
> NULL, I don't want to return that order.

select ord_no
from OE_HEADER h
where not exists (
select *
from OE_LINE l
where l.ord_type = h.ord_type and l.ord_no = h.ord_no
)

If the status column can have only values 'Y' and NULL, then this will give
you the orders that have all line items with status 'Y':

SELECT H.ord_no
FROM OE_HEADER AS H
JOIN OE_LINE AS L
ON H.ord_no = L.ord_no
GROUP BY H.ord_no
HAVING COUNT(*) = COUNT(L.status)

If the column can have values other than 'Y' and NULL, then you can use the
following:

SELECT H.ord_no
FROM OE_HEADER AS H
JOIN OE_LINE AS L
ON H.ord_no = L.ord_no
GROUP BY H.ord_no
HAVING COUNT(*) = (SELECT COUNT(*)
FROM OE_LINE AS L
WHERE L.ord_no = H.ord_no
AND L.status = 'Y')

HTH,

Plamen Ratchev
http://www.SQLStudio.com

I wrote:

> Artie wrote:
>
>> This sounds really easy but I'm stumped!
>> Order entry app using 2 tables: OE_HEADER and OE_LINE
>>
>> select h.ord_no, l.status
>> from OE_HEADER h inner join OE_LINE l
>> on h.ord_no = l.ord_no
>> where l.status = 'Y'
>
> (Side note: I recognize the app; the join should also include ord_type)
>
>> I would like to return an ord_no when ALL the line items have a status = 'Y'
>> The default value for status is NULL
>>
>> So if an order has 5 line items, 4 have a status ='Y' but 1 has status =
>> NULL, I don't want to return that order.
>
> select ord_no
> from OE_HEADER h
> where not exists (
> select *
> from OE_LINE l
> where l.ord_type = h.ord_type and l.ord_no = h.ord_no
> )

Oops, that's obviously missing a bit:

select ord_no
from OE_HEADER h
where not exists (
select *
from OE_LINE l
where l.ord_type = h.ord_type and l.ord_no = h.ord_no
and not (l.status = 'Y')
)

Thanks Plamen, your suggestions work well.


"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message
news:Tt-dnRU0bcKQpULanZ2dnUVZ_gGdnZ2d@speakeasy.net...
> If the status column can have only values 'Y' and NULL, then this will
> give you the orders that have all line items with status 'Y':
>
> SELECT H.ord_no
> FROM OE_HEADER AS H
> JOIN OE_LINE AS L
> ON H.ord_no = L.ord_no
> GROUP BY H.ord_no
> HAVING COUNT(*) = COUNT(L.status)
>
> If the column can have values other than 'Y' and NULL, then you can use
> the following:
>
> SELECT H.ord_no
> FROM OE_HEADER AS H
> JOIN OE_LINE AS L
> ON H.ord_no = L.ord_no
> GROUP BY H.ord_no
> HAVING COUNT(*) = (SELECT COUNT(*)
> FROM OE_LINE AS L
> WHERE L.ord_no = H.ord_no
> AND L.status = 'Y')
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com

>> I would like to return an order_nbr when ALL the line items have an order_status = 'Y' The default value for order_status is NULL <<

SELECT H.order_nbr
FROM OrderHeaders AS H
WHERE 'Y'
= ALL (SELECT order_status
FROM OrderDetails AS D
WHERE H.order_nbr = D.order_nbr);

This is a little used construct in Standard since SQL-89. I assume
that status was suppose dot be order_status and not martial_status;
that you are not really storing line numbers from a form, but the
order details, etc,

--CELKO-- wrote:

> I assume
> that status was suppose dot be order_status and not martial_status;

The database in question was not designed by the poster, but rather
designed by another company and sold to many clients. Renaming the
columns is not in the cards.

> that you are not really storing line numbers from a form, but the
> order details, etc,

We had this discussion just a week or two back; I see no reason to
repeat it at this time.