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 __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Join/ QUERY HEADACHE problem
Can anyone help me with this??? -Original Message- From: Joshua Tipton [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 25, 2002 9:28 AM 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 __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Join/ QUERY HEADACHE problem
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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
