April 9, 2020 at 1:30 am
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
April 9, 2020 at 5:57 am
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 want to be the very best
Like no one ever was
April 9, 2020 at 12:42 pm
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
April 9, 2020 at 4:31 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 12, 2020 at 3:13 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply