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.
Below was the expected result
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