Issue with case statement

  • mcfarlandparkway

    SSCertifiable

    Points: 7642

    i have a table A where the data looks like

    ID        Name      Phone            CODE

    15674     AUSN      +14412984775     HT

    15674     MAN       +13857747488     JK

    67866     AUSN      +74666378838     HT

    98765     AUSN      +72366747848     HT

    Another table called B. where we join A and B based on ID column

    Table B data

    ID

    15674

    67866

    98765

    i am trying to write a case statement, we have duplicates on the A table with ID 15674, I need to select phone number based on ID column.

    if we see in the table A with unique ID 67866  and 98765  no issue. but with 15674 there are 2 records, like these there are duplicates in the A table

    i want to select phone number for this 15674, based on case statement.

    i tried below still returning duplicates

    CASE WHEN  A.Name  in ('AUSN','MAN') and  A.CODE = 'HT' THEN A.Phone

    WHEN A.Name = 'AUSN' and  A.CODE = 'JK' THEN A.Phone

    ELSE NULL END as PhoneNumber

  • ktflash

    Ten Centuries

    Points: 1127

    Even if you use case when it will still produce duplicates, because ur join condition is what produces the duplicates: the not unique IDs

    You have to filter them instead of using case when.

    I would have used ur case when as a filter in the where clause,  heres a SQL statement for those who want to test (which would have been nice if OP could post something like this next time please)

    create table #A (
    id nvarchar(5)
    ,"Name" nvarchar(50)
    ,Phone nvarchar(50)
    ,Code nvarchar(2)
    )
    insert into #A
    values ('15674','AUSN ','+14412984775','HT')
    ,('15674','MAN','+13857747488','JK')
    ,('98765','AUSN ','+74666378838','HT')


    create table #B (
    id nvarchar(5)
    )

    insert into #b
    values ('15674')
    ,('67866')
    ,('98765')


    select *
    from #A
    left join #B on #A.id = #B.id
    where #A.Name in ('AUSN','MAN') and #A.CODE = 'HT'
    or #A.Name = 'AUSN' and #A.CODE = 'JK'

    I might be missing something, if so correct me

    I wanna be the very best
    Like no one ever was

  • mcfarlandparkway

    SSCertifiable

    Points: 7642

    Still duplicates.

    My requirement is to check if single ID has 2 rows in A table and if Name is ('AUSN','MAN') and code will be different for AUSN and MAN.

    if that's the case i want to pick only one record that is MAN reacord, not AUSN record.

    15674     AUSN      +14412984775     HT

    15674     MAN       +13857747488     JK

  • Jeffrey Williams

    SSC Guru

    Points: 88448

    You can do this with an OUTER APPLY instead of a CASE expression:

    SELECT *
    FROM TableB b
    OUTER APPLY (SELECT TOP 1
    *
    From TableA a
    Where a.ID = b.ID
    Order By
    a.Name desc
    , a.Code
    ) As p
    WHERE ...

    You may need to adjust the order by - depends on your other requirements.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • RC

    Grasshopper

    Points: 12

    CREATE TABLE tabA (
    ID VARCHAR(5)
    ,[Name] VARCHAR(50)
    ,Phone VARCHAR(50)
    ,Code VARCHAR(2)
    )

    INSERT INTO TABA
    VALUES
    ('15674','AUSN ','+14412984775','HT')
    ,('15674','MAN','+13857747488','JK')
    ,('98765','AUSN ','+74666378838','HT')
    ,( '67866' ,'AUSN' , '+74666378838 ','HT')
    ,( '67866' ,'MAN' , '+74666378838 ','HT')

    CREATE TABLE tabB (
    id VARCHAR(5)
    )

    INSERT INTO tabB
    VALUES ('15674')
    ,('67866')
    ,('98765')



    ;WITH getDetails
    AS
    (
    SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [Name] DESC) AS rn, ID,[Name]
    FROM tabA
    )

    SELECT * FROM tabB b
    INNER JOIN getDetails gd ON gd.ID = b.ID
    WHERE gd.rn = 1

    • This reply was modified 1 month, 2 weeks ago by  RC.
    • This reply was modified 1 month, 2 weeks ago by  RC.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply