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.