Field holding both father and child

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)

Field holding both father and child

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2011-05-26 : 05:40:39

I have this table structure
create table
(
id
,team_code
,team_name
,group_code
,group_name
)

This table serves as lookup table, and it also holds hierarchy of a department like father-child relationship.

I have to do join with a field from another table which contains both team code and group name mixed.

Am I to do two different joins on the tables? one for team_code and one for group_code Or do we have an optimised way to do the joins to cater for both team_code and group_code at the same time?

Many thanks.

I sign for fame not for shame but all the same, I sign my name.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-26 : 06:54:46

It may be possible to do a single join, but hard to say without seeing the structure of the other table and some sample data. Does the other table have something to distinguish what is a group code and what is a team code?

If "team code and group names are mixed", does that mean that the data is fundamentally incorrect, by having assigned some group names to team codes instead of group codes? If the data is incorrect, there may be nothing that can salvage it.

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2011-05-26 : 08:33:25

quote:

hard to say without seeing the structure of the other table and some sample data. Does the other table have something to distinguish what is a group code and what is a team code?

No, there is nothing to distinguish between group code and team code.

Kindly see below if this can help.. Thanks

select
a.tran_id
,a.tran_date
....
...
, b.team_name
, b.group_bame
from
tableA a
left outer join tableB b on a.code = b.team_code
left outer join tableB b on a.code = b.group_code

Note
Since tableB is an hierachy table, both team_code and group_code are necessary when I want to roll-up figures.

I dont't want to join to have two different entries like these {b.team_name, b.group_bame}
Say if am joining through team_code then automatically, I have group code and if through group_code, then may be team_code is null.

See sample report below
tran_id tran_date ....... group team
1 2011-04-01 ...... 10 0 <- tableA code field = group code
2 2011-04-01 ...... 10 10 <- TableA code field = team_code

Doing roll up, group sum will 20 and team sum will be 10.

Thanks

quote:

If "team code and group names are mixed", does that mean that the data is fundamentally incorrect, by having assigned some group names to team codes instead of group codes? If the data is incorrect, there may be nothing that can salvage it.

They are actually cleaned and correct data, the application saves it that way.

I sign for fame not for shame but all the same, I sign my name.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-26 : 11:19:57

Maybe this list will help you post your DDL, DML and expected output so we can help you better:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2011-05-26 : 12:47:05

Thanks Lamprey

Kindly see below

DECLARE @team table (team_code int,group_code int, group_name varchar(20), deptcode varchar(20) )
insert into @team (team_code,group_code, group_name, deptcode)
select 1, 100, 'A', 'ABC' UNION ALL
select NULL, 200, 'B', 'ABD' UNION ALL
select 3, 300, 'C', 'ABE' UNION ALL
select 4, 300, 'D', 'ABE' UNION ALL
select NULL, 500, 'E', 'ABG' UNION ALL
select 2, 600, 'F', 'ABH' UNION ALL

Note: @team join fields (i) team_code, (ii) group_code

declare @transaction table (id int , desc varchar(100), code int)
insert into @transaction (id , desc, code)
select 1, 'Abacus', 1 UNION ALL
select 2, 'Lamprey',200 UNION ALL
select 3, 'sunitabeck', 3 UNION ALL
select 4, 'spirit1',2 UNION ALL
select 5, 'visakh16',600 UNION ALL
select 6, 'lol',300 UNION ALL

Note: @transaction join fields (i) code

My expected output

1, 'Abacus', 'ABC'
2, 'Lamprey', 'ABD'
3, 'sunitabeck', 'ABE'
4, 'spirit1', 'ABH'
5, 'visakh16', 'ABH'
6, 'lol', 'ABE'

I sign for fame not for shame but all the same, I sign my name.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-26 : 13:31:28

[code]DECLARE @team table (team_code int,group_code int, group_name varchar(20), deptcode varchar(20) )
insert into @team (team_code,group_code, group_name, deptcode)
select 1, 100, 'A', 'ABC' UNION ALL
select NULL, 200, 'B', 'ABD' UNION ALL
select 3, 300, 'C', 'ABE' UNION ALL
select 4, 300, 'D', 'ABE' UNION ALL
select NULL, 500, 'E', 'ABG' UNION ALL
select 2, 600, 'F', 'ABH'

--Note: @team join fields (i) team_code, (ii) group_code

declare @transaction table (id int , [desc] varchar(100), code int)
insert into @transaction (id , [desc], code)
select 1, 'Abacus', 1 UNION ALL
select 2, 'Lamprey',200 UNION ALL
select 3, 'sunitabeck', 3 UNION ALL
select 4, 'spirit1',2 UNION ALL
select 5, 'visakh16',600 UNION ALL
select 6, 'lol',300

--Note: @transaction join fields (i) code

--My expected output
--
--1, 'Abacus', 'ABC'
--2, 'Lamprey', 'ABD'
--3, 'sunitabeck', 'ABE'
--4, 'spirit1', 'ABH'
--5, 'visakh16', 'ABH'
--6, 'lol', 'ABE'

select DISTINCT
t1.id,
t1.[desc],
coalesce(t2.deptcode,t3.deptcode,'error') as deptcode
from @transaction as t1
left join @team as t2 on t2.team_code=t1.code
left join @team as t3 on t3.group_code=t1.code[/code]

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

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2011-05-26 : 14:08:07

Thanks Boss @webfred.. Yes Yak 'n' Roll...

@All, Thanks..

I sign for fame not for shame but all the same, I sign my name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources