Click here to Skip to main content
12,399,561 members (61,109 online)
Rate this:
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,,,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

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