Click here to Skip to main content
12,950,618 members (59,024 online)
Rate this:
Please Sign up or sign in to vote.
See more:
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,,,b.mark
from @t1 a 
inner join @t2 b
on ( or = 0) and =

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 4:15am
Member 8070578 13-Dec-12 10:34am
Common Table Expression

1 solution

Rate this: bad
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,,, b.mark
  from @t1 as a inner join
    @t2 as b on = @target_site and = and
      ( = or ( = 0 and not exists ( select 42 from @t2 where site = @target_site and id = ) ) )

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

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 5,984
CHill60 3,460
Maciej Los 3,053
Jochen Arndt 1,975
ppolymorphe 1,820

Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 14 Dec 2012
Copyright © CodeProject, 1999-2017
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