Select from multi Tables - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Select from multi Tables

Author

Topic

mahsa_mr
Starting Member

22 Posts

Posted - 2010-01-15 : 14:21:32

Hi ,

I have a table a: with following collums:

Table a:

perid edesc
----- ------
1 ea
1 eb
1 ec
2 ea
2 ec

Table b:

perid ddesc
----- ------
1 da
2 da
2 dc

now wants to show it as a foolowing :

perid edesc ddesc
------ ------ ------
1 ea da
1 eb null
1 ec null
2 ea da
2 ec dc

may you advice ?
Thank you

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 14:25:34

For perid1, Why does "da" have to be corresponding to "ea" and not "eb" or "ec"...

mahsa_mr
Starting Member

22 Posts

Posted - 2010-01-15 : 14:38:51

if you suppose that table "e" is the earning and table "d" are deductions , so person id : 1 can be have some earning and deduction and it's not fix for all month .

and i want to have a result like that to show in pay slip.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-15 : 14:58:16

select
e.perid,
e.edesc,
d.ddesc
from table_a as e
left join table_b as d
on stuff(e.edesc,1,1,'') = stuff(d.ddesc,1,1,'')
order by e.perid,e.edesc

No, you're never too old to Yak'n'Roll if you're too young to die.

shan
Yak Posting Veteran

84 Posts

Posted - 2010-01-15 : 15:03:24

see if this works

declare @t1 table(id int,name varchar(5))
declare @t2 table(id int,name varchar(5))

insert into @t1
select '1', 'ea'
union all
select '1', 'eb'
union all
select '1', 'ec'
union all
select '2', 'ea'
union all
select '2', 'ec'
--select * from @t

insert into @t2

select '1','da'
union all
select '2', 'da'
union all
select '2', 'dc'

select a.id,a.name,b.name
from
(
select id,name ,substring(name,2,1) as sub_name,rank() over(partition by id order by id,substring(name,2,1)) as rnk from @t1
)
a
left outer join
(
select id,name,substring(name,2,1) as sub_name,rank() over(partition by id order by id,substring(name,2,1)) as rnk from @t2
)b
on a.rnk = b.rnk and a.id =b.id

-Shan

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 15:08:52

But I really doubt if OP's actual data is similar to the sample he has shown here...especially based on his statement here.

quote:
if you suppose that table "e" is the earning and table "d" are deductions

My guess is he wud need something like this

select a.id,sum(a.edesc),sum(b.ddesc)
from table1 a
inner join table2 b on a.id = b.id
group by a.id

But of course, I'm just speculating based on OP's comments about earnings and deductions.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-15 : 15:14:36

I agree.
OP should give a better example.

No, you're never too old to Yak'n'Roll if you're too young to die.

mahsa_mr
Starting Member

22 Posts

Posted - 2010-01-15 : 23:49:57

Hi

Thanks all for reply .

The Example of this is completely like all employee pay slip , one column hold the earning and one column for deduction .

earning column is a table that have a person id , earning description and earning amount
deduction column is a table that have a person id , deduction description and deduction amount

now I want to show it in report and need a query to have these information together.

please let me know if need more clarification .

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-16 : 08:40:06

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources