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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
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