Joins - 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)

Joins

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2006-07-25 : 09:21:36

I have the following code which joins two temp tables

create table #temptable (folder_id varchar(100), orders smallint)
create table #temptable1 (folder_id varchar(100), actual smallint)

insert into #temptable (folder_id, orders) select distinct d.pack_id, count(distinct order_id) as 'orders'
from tblDocuments d
group by d.pack_id order by d.pack_id

insert into #temptable1 (folder_id, actual) select distinct d.folder, count(distinct order_id) as 'actual'
from tblDocuments d
left join tblFilenames f on f.code = convert(varchar(20), d.pack_id)
group by d.pack_id, f.mydesc, d.folder order by d.folder

select * from #temptable
select * from #temptable1

drop table #temptable
drop table #temptable1

This outputs for #temptable

folder_id | orders
------------------------
10 | 4
16 | 5
2 | 1
21 | 4
4 | 1
5 | 6
50 | 1
69 | 1
7 | 1
8 | 4

This outputs for #temptable1

folder_id | orders
------------------------
10 | 3
16 | 5
2 | 1
21 | 4
4 | 1
5 | 6
50 | 1
69 | 1
7-8-10 | 1
8 | 3

I would like to join the tables so I get the following output which is based mainly on the second table but should an entry from the first not be there then to output original count and then actual as zero.

The join in the code excludes the 7 folder_id as it doesn't have any actual packs now. See below
folder_id | orders | actuals
------------------------------
10 | 4 | 3
16 | 5 | 5
2 | 1 | 1
21 | 4 | 4
4 | 1 | 1
5 | 6 | 6
50 | 1 | 1
69 | 1 | 1
7-8-10 | 0 | 1
8 | 4 | 3
| 27 | 26

Can you help please

jparker
Posting Yak Master

118 Posts

Posted - 2006-07-25 : 09:32:58

Apologies I forgot to include the joining of the temp tables code which is as follows : -

select t1.folder_id, IsNull(t.orders, 0) as orders, t1.actual
from #temptable1 t1
left join #temptable t on t.folder_id = t1.folder_id
union all
select ' ', sum(t.orders), sum(t1.actual)
from #temptable1 t1
left join #temptable t on t.folder_id = t1.folder_id

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-25 : 10:28:03

Erm, what is the question? The way I read it, you already have everything you need.

Do you want folder_id 7 to be included? If so, what should that row look like? If not, what?

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

jparker
Posting Yak Master

118 Posts

Posted - 2006-07-25 : 10:36:09

Your correct I don't have folder_id 7

The correct results would look something like this

older_id | orders | actuals
------------------------------
10 | 4 | 3
16 | 5 | 5
2 | 1 | 1
21 | 4 | 4
4 | 1 | 1
5 | 6 | 6
50 | 1 | 1
69 | 1 | 1
7 | 1 | 0
7-8-10 | 0 | 1
8 | 4 | 3
| 27 | 27

Where 7 | 1 | 0 is the new line. The problem being is that pack_id does equal folder_id which as there isn't a match doesn't bring in the value

Its too hot and my brain is failing to work so would appreciate how to get this working

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-25 : 11:10:17

You probably just need to use full join rather than left join, and then handle the nulls. Something like this...

select isnull(t1.folder_id, t.folder_id), IsNull(t.orders, 0) as orders, isnull(t1.actual, 0) as actual
from #temptable1 t1
full join #temptable t on t.folder_id = t1.folder_id
union all
select ' ', sum(t.orders), sum(t1.actual)
from #temptable1 t1
full join #temptable t on t.folder_id = t1.folder_id

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

jparker
Posting Yak Master

118 Posts

Posted - 2006-07-25 : 11:21:08

Thanks that will probably be the best solution so that I can move on to something new :)

Thanks

J

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-25 : 11:30:00

Incidentally, it may be more efficient to use a group by and rollup to get your totals (rather than a union).

select
    case when grouping(folder_id) = 1 then '' else folder_id end as folder_id,
    sum(orders) as orders,
    sum(actual) as actual
from (
    select
        isnull(a.folder_id, b.folder_id) as folder_id,
        isnull(b.orders, 0) as orders,
        isnull(a.actual, 0) as actual
    from #temptable1 a full outer join #temptable b on a.folder_id = b.folder_id) c
group by folder_id
with rollup
order by grouping(folder_id), folder_id

Of course, it may be even more efficient if you get your totals from your front-end (if applicable).

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources