SQL Server – How to Filter Records with Same ID but Different Column Values

group bypartitioningsql serversubquery

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

Best Answer

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

Related Question