My join doesn't work - 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

Transact-SQL (2000)

My join doesn't work

Author

Topic

Baquardie
Starting Member

8 Posts

Posted - 2004-08-19 : 10:56:31

Hello friends

Here is my problem. I have two table containting each a set of rows. The first one have the budget sales and the second one have the real sales. I want to join the two table to create ONE table with both budget and real sales.

Before i post my query that doesn't work, i will explain it.

The two table are : Sales & Budget . The key is a match of a lot of string column (client, year, week, brand, size, group).

There can be a budget but no sales or a sales without a budget.

select     a.year, 
		 a.week, 
		 a.ClientNumber, 		 
		 a.depot, 
		 a.brand, 
		 a.size, 
		 a.group, 
		 sum(bc_cs) as "BC_CS", 
		 sum(bc_mt_cdn) as "BC_MT_CDN",
		 sum(de_cs) as "DE_CS", 
		 sum(de_mt_cdn) as "DE_MT_CDN"
from budgetfin a full outer join Sales b on 
		(a.week = b.week and 
		 a.year = b.year and 
		 a.ClientNumber = b.ClientNumber and
                 a.brand = b.brand and 
		 a.size = b.size and 
		 a.group = b.group and 
		 a.banner = b.banner)
where (a.year = 2004 or a.year = 2005) and 
       a.type = 'VENTES' and 
       b.type = 'VENTES' and
       a.ClientNumber = '3122' or b.ClientNumber = '3122')
group by a.year, 
	 a.week, 
	 a.ClientNumber, 
	 b.ClientNumber, 
	 a.depot, 
	 a.brand, 
	 a.size, 
	 a.group
order by a.year, 
	 a.ClientNumber, 
	 a.brand, 
	 b.ClientNumber, 
	 a.size, 
	 a.group, 
	 a.week

my query is only giving me back rows that are in both table, which is NOT what I need.

Thanks for any help i'm clueless here.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-19 : 10:59:40

Are you trying to combine them to one record set??
If so... try Union ALL

Select blah From realSales
Union ALl
Select blah from budgetSales

If thats not it, try posting some sample data for us to play with and a desired result.

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-19 : 11:00:54

P.S. Is that shrek & the dragon?? nice

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-19 : 11:11:50

i would use UNION as well. avoid full outer joins as much as possible, in my opinion. they are not a "logically sound" way to combine the contents of two tables.

the best way to handle the old "budget vs actual" when you may have both or one or the other is to union the resultsets together, and then GROUP BY the columns you wish to consolidate over.

i.e., you start with this:


select account, 0 as actual, Amt as BUdget
from Budgets
UNION ALL
select account, Amt as Actual, 0 as Budget
from Actuals

see how it has actuals and budgets, and spreads out the two columns with zeros as needed? then you just do this for your final result:


select account, sum(actual), sum(budget)
from
 (above SQL) a
group by account

Does this help? can you apply it to your situation? There really should never be a good reason to use a FULL OUTER JOIN. when you do, EVERY column needs an ISNULL() around it.

- Jeff

Baquardie
Starting Member

8 Posts

Posted - 2004-08-19 : 11:23:49

quote:
Originally posted by Seventhnight

P.S. Is that shrek & the dragon?? nice

Corey

yes it is!!!

Baquardie
Starting Member

8 Posts

Posted - 2004-08-19 : 11:27:18

quote:
Originally posted by jsmith8858

i would use UNION as well. avoid full outer joins as much as possible, in my opinion. they are not a "logically sound" way to combine the contents of two tables.

the best way to handle the old "budget vs actual" when you may have both or one or the other is to union the resultsets together, and then GROUP BY the columns you wish to consolidate over.

i.e., you start with this:

select account, 0 as actual, Amt as BUdget
from Budgets
UNION ALL
select account, Amt as Actual, 0 as Budget
from Actuals

see how it has actuals and budgets, and spreads out the two columns with zeros as needed? then you just do this for your final result:

select account, sum(actual), sum(budget)
from
(above SQL) a
group by account

Does this help? can you apply it to your situation? There really should never be a good reason to use a FULL OUTER JOIN. when you do, EVERY column needs an ISNULL() around it.

- Jeff

hey -- thanks!!!

This is a really good idea, i wasn't thinking about a two steps job..... i'm really happy, it's working!!

Here is my querys...

/* Reading data */
select year, week, ClientNumber, depot, Brand, size, group, 0 as "DE_CS", 0 as "DE_MT_CDN", sum(bc_cs) as "BC_CS", sum(bc_mt_cdn) as "BC_MT_CDN"
into TempTable
from budget
where (year = 2004 or year = 2005) and type = 'VENTES'
group by year, week, ClientNumber, depot, Brand, size, group
UNION ALL
select year, week, ClientNumber, depot, Brand, size, group, sum(de_cs) as "DE_CS", sum(de_mt_cdn) as "DE_MT_CDN", 0 AS "BC_CS", 0 as "BC_MT_CDN"
from sales
where (year = 2004 or year = 2005) and type = 'VENTES'
group by year, week, ClientNumber, depot, Brand, size, group

/* Add into the new table */
insert into DestinationTable 
select year, week, ClientNumber, depot, Brand, size, group, sum(de_cs), sum(de_mt_cdn), sum(bc_cs), sum(bc_mt_cdn)
from TempTable
group by year, week, ClientNumber, depot, Brand, size, group

thank you also Seventhnight!!

See ya,

Baquardie

ps jsmith8858 thanks for the tips about full outer joins i will try to avoid them!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources