Problem with combining two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with combining two tables

Author

Topic

function
Starting Member

12 Posts

Posted - 2010-08-27 : 05:53:07

Hi guys
I'm having a little problem getting the right query, so i really hoped you could help me with that...
So, starting we have 2 tables S and R.
The S table is as follows:

s_id (autonumber), s_idnumber (unique for every record)
=======================================================
1 | A
2 | B
3 | C
4 | D
5 | E

The R table is as follows:
r_id(autonumber), r_iatanumber, r_points, r_status
====================================================
1 | A | 20 | 2
2 | A | 30 | 1
3 | A | 40 | 2
4 | B | 10 | 2
5 | B | 30 | 2
6 | C | 20 | 2
7 | C | 50 | 2
8 | C | null | 2
9 | D | 80 | 2
10 | D | 90 | 2
11 | D | 15 | 1

All i want as a result is the following:

s_id , s_idnumber , points, , r_status
========================================================
1 | A | 60 (20+40) | 2
2 | B | 40 (10+30) | 2
3 | C | 70 (20+50+null)| 2
4 | D | 170(90+80) | 2
5 | E | 0 | 2
Any help would be appreciated guys. Thanks in advance!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-27 : 06:17:37

You should put in more explanation on how you arrive at your output.

Basis whatever you have give,, this should get you going

select 
    RTable.r_id as s_id,
    RTable.r_iatanumber as s_idnumber,
    sum(RTable.r_points)*sum(RTable.r_points) as points,
    count(RTable.r_id) as r_status
    
from
   STable join RTable  on STable.s_id=RTable.r_id
where 
    RTable.r_status=2
group by 
    RTable.r_id,
    RTable.r_iatanumber

then again.. I might be wrong since you don't have enough explanation for anyone to understand.

function
Starting Member

12 Posts

Posted - 2010-08-27 : 06:27:37

Ok. Let me explain it more to you.
I have the S table which holds the s_users with some fields inside. One of the is s_idnumber which is unique for every record.

Then, i have the R table which holds records with some of the s_users and other stuff (such as points).

All idnumbers of S table are not in R table (only some of them).
All i want to do is distinctly collect all s_users with their sum of points of the R table.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-27 : 06:31:12

only where r_status is 2?

function
Starting Member

12 Posts

Posted - 2010-08-27 : 07:18:33

yes

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-27 : 07:37:20

try the query I posted earlier.

santhosh.yamsani
Starting Member

2 Posts

Posted - 2010-08-30 : 00:16:04

select r_id,r_idnumber,sum(points),s_status from(

select r_id,r_idnumber, 0 as points,2 as s_status from #r
union
select a.r_id ,a.r_idnumber , sum(b.s_points) as points,b.s_status from #R a
Right outer join #S b
on a.r_idnumber=b.s_idnumber
where b.s_status=2
group by b. s_status,a.r_id ,a.r_idnumber ) t
group by t.s_status,t.r_id ,t.r_idnumber

santhosh

santhosh.yamsani
Starting Member

2 Posts

Posted - 2010-08-30 : 00:22:13

Hii all..!!

To increase the performance,You can adopt CTE in the above query:

Query looks like:

with CTE as(
(
select r_id,r_idnumber, 0 as points,2 as s_status from #r
union
select a.r_id ,a.r_idnumber , sum(b.s_points) as points,b.s_status from #R a
Right outer join #S b
on a.r_idnumber=b.s_idnumber
where b.s_status=2
group by b. s_status,a.r_id ,a.r_idnumber
)
select r_id,r_idnumber,sum(points),s_status from CTE
group by s_status,r_id ,r_idnumber

santhosh

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-09-04 : 00:28:58

Hi Everyone,
I have been going through this example, but cannot make sense out of it. Can any one re_do the example as required by the author. I always find it hard to solve such problems.

Thanks in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources