Click here to Skip to main content
11,478,066 members (71,328 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL SQL-Server
Please consider the following SQL

declare @t1 table(site int, id int, name varchar(2))
declare @t2 table(site int, id int, mark int)
 
insert into @t1
select 1,1,'A'
union select 1,2,'B'
union select 1,3,'C'
union select 2,2,'D'
union select 2,3,'C'
 
insert into @t2
select 1,1,10
union select 1,2,20
union select 0,3,30
union select 1,3,40
union select 2,3,40
union select 2,3,40
 
select distinct a.site, a.id,a.name,b.mark
from @t1 a 
inner join @t2 b
on (a.site =b.site or b.site = 0) and a.id = b.id
where a.site=1

It produces the following result
site id name mark
----------------------------
1 1 A 10
1 2 B 20
1 3 C 30
1 3 C 40

It's correct.

But I want a person's data exactly once. The SQL should first check whether there is an entry for a person in @t2 for a specific site. If entry is found, then use it. If not, the mark of that person will be the person's mark who has the same name in site 0.

In this case, I want the result as follows.
site id name mark
----------------------------
1 1 A 10
1 2 B 20
1 3 C 40

But if (1,3,40) isn't in @t2, The result should be as follows.
site id name mark
----------------------------
1 1 A 10
1 2 B 20
1 3 C 30

How can I do this?
I can do it using CTE.
So please provide me a faster way.
I'll run it on about 100 millions rows.
Posted 13-Dec-12 5:15am
JakirBB1.3K
Comments
Member 8070578 at 13-Dec-12 10:34am
   
Common Table Expression

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I have found solution. I have to write query as follows. But still in confusion about performance.
declare @target_site as Int = 1
select distinct a.site, a.id, a.name, b.mark
  from @t1 as a inner join
    @t2 as b on a.site = @target_site and a.id = b.id and
      ( a.site = b.site or ( b.site = 0 and not exists ( select 42 from @t2 where site = @target_site and id = a.id ) ) )
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Frankie-C 160
1 Sergey Alexandrovich Kryukov 149
2 Sascha Lefèvre 145
3 F-ES Sitecore 130
4 siddartha kopparapu 103
0 Sergey Alexandrovich Kryukov 7,675
1 OriginalGriff 7,321
2 Sascha Lefèvre 3,034
3 Maciej Los 2,491
4 Richard Deeming 2,325


Advertise | Privacy | Mobile
Web02 | 2.8.150520.1 | Last Updated 14 Dec 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100