That isn't working because of the :
where region = '04' and [desc] like '%Miss%'
group by region, [year], [month]
The records that would be included by the outer join have null values for
those fields.
You need something roughly (very) like this:
select tbl_monthyearcorp.[month], tbl_monthyearcorp.[year],
atable.region,
isnull(SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END),0) AS
INTCOST,
isnull(SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END),0) AS exTCOST
FROM tbl_monthyearcorp right OUTER JOIN (select * from CTMCORPAPP where
region = '04' and [desc] like '%Miss%') atable ON tbl_monthyearcorp.m_id=
atable.memomonth AND tbl_monthyearcorp.y_id = atable.memoyear
group by region, [year], [month]
-----Original Message-----
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: 25 April 2002 14:28
To: CF-Talk
Subject: SQL Join/ QUERY HEADACHE problem
I have two tables that I am trying to join and it is killing me.
1st Table has the following columns
Fields in 04_TSG_DISPOS.tbl_monthyearcorp
COLUMN NAME DATA TYPE LENGTH NULLS ALLOWED
monthid int identity 4 No
month char 10 Yes
year numeric 20 Yes
m_id int 4 Yes
y_id int 4 Yes
The second table has the following columns that I need to join
Fields in 04_TSG_DISPOS.CTMCORPAPP
COLUMN NAME DATA TYPE LENGTH NULLS ALLOWED
REGION nvarchar 4 Yes
DISTRICT nvarchar 4 Yes
SLIC nvarchar 16 Yes
Desc nvarchar 60 Yes
Cost float 8 Yes
memomonth char 2 No
memoyear char 2 No
The query that I need is as follows
select tbl_monthyearcorp.[month], tbl_monthyearcorp.[year],
ctmcorpapp.region,
isnull(SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END),0) AS
INTCOST,
isnull(SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END),0) AS exTCOST
FROM tbl_monthyearcorp right OUTER JOIN CTMCORPAPP ON tbl_monthyearcorp.m_id
= CTMCORPAPP.memomonth AND tbl_monthyearcorp.y_id = CTMCORPAPP.memoyear
where region = '04' and [desc] like '%Miss%'
group by region, [year], [month]
I have tried right outer joins and it is not getting what I want. What I am
wanting is that all months and years will display even if there is no datain
the ctmcorpapp.
so the output should show
Region Month Year INTCOST EXTCOST
4 January 2001 0 or Null 0 or Null
4 February 2001 12 15
All the way down through all of the months. I think it has something to do
with the case statement and I am hoping that someone can please help me with
this I am dying on this one. If you need to call me to ask me anyquestions
or would like me to call you I can just email me off list.
Joshua Tipton
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
