How to to do a conditional join-CodePudding

I have two tables and join key is Id, below are the details. I need to insert the data when in table Test Orgorder is 1 and [ORGTYPE] is 'ABC', then I need to take values from LEAD. When [ORGTYPE] is 'ABC' and Orgorder is not equal to 1, then take value of lead where ORGID is minimum.

Issue is whenever I am joining the two tables I am getting both value of id 100, but I want a single row for id 100. and for ID = 101 as minimum value of OrgID = 0 the whatever value against Lead.

CREATE TABLE TES(
ID int,
Name Varchar(10)
)
Insert into TES (ID,NAME) VALUES (100,'XYZ')
Insert into TES (ID,NAME) VALUES (101,'XYZ')

CREATE TABLE [dbo].[TEST](
    [ORGORDER] [int] NULL,
    [Id] [int] NOT NULL,
    [ORGTYPE] [varchar](30) NULL,
    ORGID INT NULL,
    [LEAD] [decimal](19, 2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE] ,ORGID, [LEAD]) VALUES (1, 100, N'ABC',1, NULL)
GO
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE],ORGID, [LEAD]) VALUES (0, 100, N'ABC',2, 0)
GO
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE],ORGID, [LEAD]) VALUES (0, 100, N'ACD',1, NULL)
GO
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE],ORGID, [LEAD]) VALUES (0, 101, N'ABC',0, 1)
GO
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE],ORGID, [LEAD]) VALUES (2, 101, N'ABC',4, NULL)

GO

Query I am running

SELECT  * FROM TES LEFT JOIN TEST on TES.ID=TEST.ID
AND TEST.ORGTYPE='ABC' and (TEST.ORGORDER=1
OR OrgOrder=(
Select top 1 [LEAD]  from TEST 
where ORGID in (select min(ORGID) from TEST
where OrgType = 'ABC' group by ID)))

My result is

Expected result is

CodePudding user response：

What I'm reading is that you want to grab ORGID = 1 if it's available and then fall back based on ORGORDER otherwise. You'll only be keeping a single row per ID.

with data as (
    SELECT TES.NAME, TEST.*,
        row_number() over (
            partition by TEST.ID
            order by case when TEST.ORGID = 1 then 1 else 2 end,
                     TEST.ORGORDER
        ) as rn
    FROM TES INNER JOIN TEST on TEST.ID = TES.ID
    WHERE TEST.ORGTYPE = 'ABC'
)
select * from data where rn = 1;

Nothing here suggests to me that you want a left join.

Following your original line of thought you might have come up with something like this instead:

select TEST.ID, TEST.ORGORDER, TEST.ORGTYPE, TEST.ORGID,
    case when TEST.ORGID = 1 then TEST."LEAD" else (
        select "LEAD" from TEST
        where ID = TEST2.ID and ORGORDER = TEST2.MIN_ORGORDER
    ) end as "LEAD"
from TES inner join TEST on TEST.ID = TES.ID
    inner join (
        select ID,
            min(ORGORDER) as MIN_ORGORDER,
            max(case when ORGID = 1 then 'Y' else 'N' end) as HAS_ORGID1
        from TEST
        group by ID
    ) as TEST2 on TEST2.ID = TEST.ID
where TEST.ORGID = 1 or TEST2.HAS_ORGID1 = 'N' and TEST2.MIN_ORGORDER = TEST.ORGORDER;

The first is clearer and likely more efficient.

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=252da6d3bd2a233d161fce5c828355c7