0

Can anyone help me to solution for below issue.

create table [order1] (order_id int, CartTransaction_Id int, Status_Id int);
insert into [order1] values
(357488,87214,1),
(357489,87214,8),
(357490,87214,1),
(357491,87214,1),
(343980,87216,1),
(357483,87216,1);

create table [Status1] (Type VARCHAR(100), Status_Id int);
insert into [Status1] values
('New','1'),
('Awaiting Approval','8'),
('New','1'),
('New','1'),
('New','1'),
('New','1');

    select
A.Order_Id, A.CartTransaction_Id, A.Status_Id, B.Type, B.Status_Id
FROM order1 A
JOIN dbo.Status1 B ON dbo.A.Status_Id = dbo.B.Status_Id
--AND B.Type = 'New' 
order by A.CartTransaction_Id

Below was the output I am getting from above query.

enter image description here

Below was the expected result

enter image description here

I need to populate the data only if status type was 'New' for all order_id's in group of CartTransaction_Id

From the giving I need to filter out CartTransaction_Id (87214) as one of the order_id status_type was on Awaiting Approval

  • Why wasn't order 357491 included? Or 357490? – scsimon Aug 21 '18 at 19:14
  • if you check order_id 357491 or 357490 both are for under 87214 (cart transaction id). As mentioned I need to populate the data only if status type was 'New' for all order_id's in group of CartTransaction_Id – heye Aug 21 '18 at 19:18
  • Why do you have the same status pair ('New', 1) repeated five times in the Status1 table? Also, are there any keys or indexes on the tables? – mathewb Aug 22 '18 at 3:12
  • The answer for SQL Server and MySQL may be different. Pick one! I'm removing the MySQL tag since the syntax does not apply. – Rick James Aug 30 '18 at 0:26
1

You could for example do this:

select
A.Order_Id, A.CartTransaction_Id, A.Status_Id, B.Type, B.Status_Id
FROM order1 A
JOIN dbo.Status1 B ON dbo.A.Status_Id = dbo.B.Status_Id
where
not exists (select 1 from order1 o1 where o1.CartTransaction_Id = A.CartTransaction_Id and o1.Status_Id != 1)
order by A.CartTransaction_Id

If you have a lot of data, then you might to want to check other alternatives for this, like max + over

1

Try this:

SELECT  A.Order_Id, A.CartTransaction_Id, A.Status_Id, B.Type, B.Status_Id
FROM    order1 A
JOIN    Status1 B
    ON A.Status_Id = B.Status_Id
OUTER APPLY (SELECT TOP 1 C.CartTransaction_Id FROM order1 C WHERE A.CartTransaction_Id = C.CartTransaction_Id AND C.Status_Id <> 1) C
WHERE   C.CartTransaction_Id IS NULL
ORDER BY A.CartTransaction_Id
0

Does your Status1 table need to have multiple entries of "NEW"... If not below query should work

select
A.Order_Id, A.CartTransaction_Id, A.Status_Id, B.Type, B.Status_Id
FROM order1 A
JOIN dbo.Status1 B ON A.Status_Id = B.Status_Id
WHERE NOT EXISTS (SELECT 1 FROM order1 c WHERE c.CartTransaction_Id = 
a.CartTransaction_Id AND c.Status_Id <> 1)
order by A.CartTransaction_Id
0

First off, you don't need that many inserts to the [Status1] table

create table [Status1] (Type VARCHAR(100), Status_Id int);
insert into [Status1] values
('New','1'),
('Awaiting Approval','8');

Will be good enough for your joins.

Here's my take on the answer using ROW_NUMBER to get what you want.

;with Orders as (
    select A.Order_Id [OrderID]
    , A.CartTransaction_Id [CartTransactionID]
    , A.Status_Id [OrderStatus]
    , B.Type [StatusType]
    , B.Status_Id [StatusID]
    , ROW_NUMBER() OVER(PARTITION BY A.CartTransaction_Id, A.Status_Id ORDER BY A.CartTransaction_Id, A.Status_Id) RowNo
from order1 a join status1 b on a.status_id = b.status_id
)
select o.OrderID
    , o.CartTransactionID
    , o.OrderStatus
    , o.StatusType
    , o.StatusID 
from orders o
Where 1=1
and o.RowNo = 1
and o.StatusID = 1 --o.StatusType='new'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.