Creating a control table - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Creating a control table

Author

Topic

tonywig
Starting Member

2 Posts

Posted - 2009-02-16 : 03:57:00

Hi all

I have a number of tables each of which has a column called "legacy_pol".
Each of the tables will contain a varying number of rows.

I also have a control table which contains a small selection of legacy_pol values against which I need to hold a count from each of the other tables.

I'm looking for a report that looks a built like ...

Legacy_Pol Table_A Table_B Table_C etc
12345 41 38 97
67890 12 16 11
etc

I am (to say the least) no expert.
I've created the control table.
I've inserted the legacy_pol values I want to collect counts for.
Now I am stuck on how to collect values from the other tables.

Any advice welcome.
Thanks
tonywig

Tony

sridhar.dbe
Starting Member

34 Posts

Posted - 2009-02-16 : 05:01:55

Is this what you want?
declare @t1 table(id varchar(10))
insert into @t1
select 'L1' as 'id' union all
select 'L2' as 'id' union all
select 'L2' as 'id' union all
select 'L2' as 'id' union all
select 'L3' as 'id' union all
select 'L3' as 'id'

declare @t2 table(id varchar(10))
insert into @t2
select 'L1' as 'id' union all
select 'L1' as 'id' union all
select 'L2' as 'id' union all
select 'L2' as 'id' union all
select 'L3' as 'id'

declare @t3 table(id varchar(10))
insert into @t3
select 'L1' as 'id' union all
select 'L1' as 'id' union all
select 'L1' as 'id' union all
select 'L2' as 'id' union all
select 'L3' as 'id' union all
select 'L3' as 'id' union all
select 'L3' as 'id'

declare @t4 table(id varchar(20))
insert into @t4
select 'L1' as 'id' union all
select 'L2' as 'id' union all
select 'L3' as 'id'

select t4.id,t1.cn,t2.cn,t3.cn from @t4 as t4,
(select id,count(id) as 'cn' from @t1 group by id) as t1,
(select id,count(id)as 'cn' from @t2 group by id) as t2,
(select id,count(id)as 'cn' from @t3 group by id) as t3
where
t4.id=t1.id
and
t4.id=t2.id
and
t4.id=t3.id

tonywig
Starting Member

2 Posts

Posted - 2009-02-16 : 05:43:29

Thanks for the help.
Technically a bit beyond me though i am happy to say I have resolved this using temp tables along the way.

I will look at your suggestion though as it is likely to be far more efficient than mine.

Thanks
Tony

Tony

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-16 : 05:47:22

hi tony,
use join than the cross join
select t4.id,t1.cn,t2.cn,t3.cn from @t4 as t4 inner join
(select id,count(id) as 'cn' from @t1 group by id) as t1 on t4.id=t1.id
inner join (select id,count(id)as 'cn' from @t2 group by id) as t2 on t4.id=t2.id
inner join (select id,count(id)as 'cn' from @t3 group by id) as t3 on t4.id=t3.id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 06:19:03

quote:
Originally posted by bklr

hi tony,
use join than the cross join
select t4.id,t1.cn,t2.cn,t3.cn from @t4 as t4 inner join
(select id,count(id) as 'cn' from @t1 group by id) as t1 on t4.id=t1.id
inner join (select id,count(id)as 'cn' from @t2 group by id) as t2 on t4.id=t2.id
inner join (select id,count(id)as 'cn' from @t3 group by id) as t3 on t4.id=t3.id

you need to use left join rather than inner join as there may be values which will not exists in one or more tables. the above code wont list them at all.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-16 : 06:23:11

quote:
Originally posted by visakh16

quote:
Originally posted by bklr

hi tony,
use join than the cross join
select t4.id,t1.cn,t2.cn,t3.cn from @t4 as t4 inner join
(select id,count(id) as 'cn' from @t1 group by id) as t1 on t4.id=t1.id
inner join (select id,count(id)as 'cn' from @t2 group by id) as t2 on t4.id=t2.id
inner join (select id,count(id)as 'cn' from @t3 group by id) as t3 on t4.id=t3.id

you need to use left join rather than inner join as there may be values which will not exists in one or more tables. the above code wont list them at all.

yes visakh,
we should use left join than inner join
just i saw the above data and written the inner join
it is better to use left join than inner join tony........

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources