Why : "select distinct order_id

[charroux]

Why :
"select distinct order_id
from orderAddresses
where order_id NOT IN
(select distinct order_id
from OrderAddresses,ShippingDetails
where OrderAddresses.order_address_id = ShippingDetails.order_address_id
and ShippingDetails.b_shipping_status != 3)"
dont' wark ?

 

[imars]

Try this:


select distinct OrderAddresses.order_id
from OrderAddresses,ShippingDetails
where OrderAddresses.order_address_id = ShippingDetails.order_address_id
and ShippingDetails.b_shipping_status = 3)

 

[charroux]

No I want the order_id
that have all their order_address_id with a b_shipping_status in the table ShippingDetails which values 3.

 

[karluk]

It looks to me as if your query will select the rows that you want, plus all rows in OrderAddresses which have no matching rows in ShippingDetails. Is that what you are seeing?

I would try adding your "not in" condition to imars's suggestion. See if the following query works:


select distinct OrderAddresses.order_id
from OrderAddresses,ShippingDetails
where OrderAddresses.order_address_id = ShippingDetails.order_address_id
and ShippingDetails.b_shipping_status = 3)
and order_id NOT IN
(select distinct order_id
from OrderAddresses,ShippingDetails
where OrderAddresses.order_address_id = ShippingDetails.order_address_id
and ShippingDetails.b_shipping_status != 3)"

 

[charroux]

no don't walk

 

[tlbroadbent]

Charroux,

You could be a little more helpful and tell us more than "It doesn't work." Tell us the result you get and the result you want. Then maybe we can determine a fix for you.

Based on yor description, I recommend the following query.

Select Distinct a.order_id
From OrderAddresses AS a
Inner Join ShippingDetails AS s
On a.order_address_id = s.order_address_id
Where ShippingDetails.b_shipping_status = 3
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.