question

ASHMITP-0361 avatar image
0 Votes"
ASHMITP-0361 asked BertZhoumsft-7490 commented

Please assist with the query to find the extra records when creating table a and table b left join

Hi ,

Please assist with the query to find the extra records when creating table a and table b left join. table a and table b both have more than one records.
Here is the script -

select a.[Fac],convert(varchar,b.fac_id + '-' + RIGHT('0000000000'+CAST(b.MRN AS VARCHAR(10)),10),50)
,surname+', '+given_names as name
from table1 a
left join table2 b
on a.[Fac] =convert(varchar,b.fac_id + '-' + RIGHT('0000000000'+CAST(b.MRN AS VARCHAR(10)),10),50)
order by a.[Fac]

The first table giving me 3277186 records . but when doing left join getting 3277290 record. My request is how to see the 104 records which is extra. Can someone help me with the query ?

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,@ASHMITP-0361

Have you tested the script below and please endorse it if it helped you, it will help someone who has a similar problem as you.

Bert Zhou

0 Votes 0 ·
NaomiNNN avatar image
0 Votes"
NaomiNNN answered NaomiNNN edited

Try as a possible query:

 ;with cte as (select a.[Fac],convert(varchar,b.fac_id + '-' + RIGHT('0000000000'+CAST(b.MRN AS VARCHAR(10)),10),50)
 ,surname+', '+given_names as name, count(*) over (partition by a.PK) as cntIds
 from table1 a
 left join table2 b
 on a.[Fac] =convert(varchar,b.fac_id + '-' + RIGHT('0000000000'+CAST(b.MRN AS VARCHAR(10)),10),50))
    
 select * from cte where cntIds > 1
 order by [Fac]
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BertZhoumsft-7490 avatar image
0 Votes"
BertZhoumsft-7490 answered

Hi,@ASHMITP-0361

Welcome to Microsoft T-SQL Q&A Forum!

Not tested, but you might try:

 select *
 From  table2
 where not exists(
 select  table1.[Fac]
 from   table1
 where table1.[Fac]=convert(varchar,table2.fac_id + '-' + RIGHT('0000000000'+CAST(table2.MRN AS VARCHAR(10)),10),50)
    
 )

Best regards,
Bert Zhou


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.