Click here to Skip to main content
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 OriginalGriff 240
1 Kamal Rocks 184
2 BillWoodruff 173
3 PIEBALDconsult 160
4 CPallini 155
0 OriginalGriff 5,695
1 DamithSL 4,506
2 Maciej Los 4,007
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,180


Advertise | Privacy | Mobile
Web04 | 2.8.141216.1 | Last Updated 14 Dec 2012
Copyright © CodeProject, 1999-2014
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