Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jan 2008
    Posts
    10

    Unanswered: Query not showing all the records?!

    I want to have all the ethnicities from the Ethnicgroup table to be displayed in a particular area which is in where clause as
    aggcourseid

    The problem is that even when I have a left join on the EthnicGroup table it will not return all the ethnicities but will only return those who were existing in that particular aggcourseid.

    i.e. If there is no African student in E2, it will not show up in the result whereas I want to display all the ethnicities and if there is no record for aggcourseid, it should show up as 0/NULL for that ethnicity.

    I have tried all sorts of joins but the problem is whenever I put in the aggcourseid in where clause, it wont bring all the records

    Code:
    declare @mpid char (13)	
    
    
    set @mpid = '011142'
    
    create table #temp_et  (
    [Year]		int NOT NULL,
    [Ethnicity] varchar(20),
    [Starts]	float(4),
    [Success]	float(4),
    [Retention]	float(4),
    [Achievement]	float(4)
    )
    
    --Year 05/06
    insert into #temp_et
    select 
    CAST(LEFT(pv.pg_expendyrid,2) AS int)		[Year],
    eg.pg_ethnicgroupname			[Ethinicity],
    sum([pvstart]) 				[Starts],
    
    (case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvstart)*1.00)*100)end)		[Success],
    (case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvcomp)*1.00)/(sum(pvstart)*1.00)*100)end)		[Retention],
    (case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvcomp)*1.00)*100)end)		[Achievement]
    --into temp_et
    from 	[FECAS].Proachieve.dbo.pv_midpoint pv
    	left join [FECAS].Proachieve.dbo.GN_AggCourseStructure gn on pv.pg_aggcourseid = gn.pg_aggcourseid
    	left join [FECAS].Proachieve.dbo.PG_ethnicGroup eg on pv.pg_ethnicgroupid = eg.pg_ethnicgroupid
    			      
    where 	pv_midpointid = @mpid	
    and 	pg_expendyrid = '05/06'		
    
    and 	pv.pg_aggcourseid LIKE 'E2%'
    
    group by 
    eg.pg_ethnicgroupname,
    pv.pg_expendyrid
    order by 
    eg.pg_ethnicgroupname
    Last edited by 5fifty5; 02-08-08 at 05:15.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you add NULL to a value, you get NULL!

    Have a look into either a coalesce() or CASE statement to do this for you...
    Code:
    Sum(CASE WHEN somefield IS NULL THEN 0 ELSE somefield)
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Excuse me for only offering half an answers, but this posting sounds suspiciously like homework to me. In spite of that, I'll still give you some help.

    Your problem is in your FROM clause. Think about what you really want to know, and the answer should fairly leap out at you.

    -PatP

  4. #4
    Join Date
    Jan 2008
    Posts
    10
    Quote Originally Posted by georgev
    If you add NULL to a value, you get NULL!

    Have a look into either a coalesce() or CASE statement to do this for you...
    Code:
    Sum(CASE WHEN somefield IS NULL THEN 0 ELSE somefield)
    The problem is that I want to get NULL which I am not getting. I guess you mis understood my problem. Its not showing up ANY records where as I want it to show NULL.

    @Pat: I am sorry I didnt get your half answer. Care to shed some more light? Would appreciate it. AS far as my understanding goes though, the problem is in WHERE Clause of aggcourseid because it does show records for all aggcourses but as soon as I want to see records for some particular course, it does not show all the ethnicities for that particular course even when I am using left/right/inner/whatever join!!
    I want the result to show all the ethnicities even if they are null

    Cheers.

    p.s: Its not homework at all.
    Last edited by 5fifty5; 02-08-08 at 10:20.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your WHERE clause perhaps contains a condition on the right side table in one of the LEFT OUTER JOINs?

    move that condition to the respective ON clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You want to see all of the PG_ethnicGroup rows. That's a bit challenging when you make it "optional" via a LEFT JOIN.

    Without knowing what you want for a final result I can only guess at how to get there. My first guess would be that you want the PG_ethnicGroup table to be your anchor table and left join against it. Another possibility might be using a FULL JOIN, but that has its own complications.

    -PatP

  7. #7
    Join Date
    Jan 2008
    Posts
    10
    @r937: I've done that as well; it makes the jions look like this

    Code:
    from 	[FECAS].Proachieve.dbo.pv_midpoint pv
    	left join [FECAS].Proachieve.dbo.GN_AggCourseStructure gn 
    	on pv.pg_aggcourseid = gn.pg_aggcourseid
    	and pv.pg_aggcourseid = @c_area
    
    	left join [FECAS].Proachieve.dbo.PG_ethnicGroup eg 
    	on pv.pg_ethnicgroupid = eg.pg_ethnicgroupid
    This condition then brings up all the records from the Ethnicgroup table, it does. BUT, then it ignores the condition that I've put in the first join which is to look into the aggcourseid.

    @Pat: I have used FULL join as well. It's all the same

    Sometimes it ignores the aggcourseid condition and brings up all the records from ethnic group table. But, I want it to bring up the records from Ethnic Group table with that condition.


    This is the output withOUT the aggcourseid condition
    Code:
    Year	Ethnicity	Starts	Success	Retention	Achievement
    5	Any Other	241.0	73.859001	87.552002	84.360001
    5	Bangladeshi	49.0	61.223999	87.754997	69.766998
    5	Black African	1029.0	69.388	83.478996	83.120003
    5	Black Caribbean	220.0	56.363998	78.181999	72.093002
    5	Black Other	85.0	68.235001	83.528999	81.690002
    5	Chinese	113.0	77.875999	94.690002	82.242996
    5	Indian	491.0	72.301003	89.612999	80.681999
    5	Mixed	285.0	64.912003	82.105003	79.059998
    5	Other Asian	177.0	87.570999	92.089996	95.092003
    5	Pakistani	283.0	55.477001	76.324997	72.684998
    5	Unknown	676.0	84.763	92.160004	91.973999
    5	White	8607.0	70.407997	83.547997	84.272003
    
    (12 row(s) affected)

    This is the output when i put in the aggcourseid condition

    Code:
    Year	Ethnicity	Starts	Success	Retention	Achievement
    5	Any Other	6.0	100.0	100.0	100.0
    5	Black African	8.0	62.5	87.5	71.429001
    5	Black Caribbean	9.0	44.444	88.889	50.0
    5	Chinese	2.0	50.0	100.0	50.0
    5	Indian	17.0	82.352997	88.235001	93.333
    5	Mixed	13.0	69.231003	76.922997	90.0
    5	Other Asian	2.0	50.0	100.0	50.0
    5	Pakistani	11.0	100.0	100.0	100.0
    5	Unknown	20.0	80.0	90.0	88.889
    5	White	554.0	85.017998	91.696999	92.717003
    
    (10 row(s) affected)

    You can see 2 rows are missing when I've put in the condition.
    Last edited by 5fifty5; 02-11-08 at 05:10.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that doesn't look like what you originally posted:
    Code:
    			      
    where 	pv_midpointid = @mpid	
    and 	pg_expendyrid = '05/06'		
    
    and 	pv.pg_aggcourseid LIKE 'E2%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2008
    Posts
    10
    well c_area is a variable defined as 'E2%'. That doesnt make a difference.
    Last edited by 5fifty5; 02-11-08 at 06:08.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

  11. #11
    Join Date
    Jan 2008
    Posts
    10
    Yeah, thanks for the find. I did it there as well and thanks to Peso.
    Last edited by 5fifty5; 02-11-08 at 11:43.

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    OK, How about this. Back up and remove parts of the where clause to see what those parts are filtering out. You will have to add those columns to the select clause to see what the actual values are, of course.

  13. #13
    Join Date
    Jan 2008
    Posts
    10
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97009

    Thanks to Peso

    Originally posted by Peso
    [br]
    Code:
    DECLARE @mpID CHAR(13)
    
    SET @mpID = '011142'
    
    CREATE TABLE	#Temp
    		(
    			[Year] INT,
    			[Ethnicity] VARCHAR(20),
    			[Starts] FLOAT(4),
    			[Success] FLOAT(4),
    			[Retention] FLOAT(4),
    			[Achievement] FLOAT(4)
    		)
    
    INSERT		#Temp
    		(
    			[Year],
    			[Ethnicity],
    			[Starts],
    			[Success],
    			[Retention],
    			[Achievement]
    		)
    SELECT		LEFT(pv.pg_expendyrid, 2),
    		eg.pg_ethnicgroupname,
    		sum(pvstart),
    		sum(case when pvstart = 0 THEN 0.0 Else 100.0 * pvach / pvstart end),
    		sum(case when pvstart = 0 THEN 0.0 Else 100.0 * pvcomp / pvstart end),
    		sum(case when pvstart = 0 THEN 0.0 Else 100.0 * pvach / pvcomp end)
    FROM		[FECAS].Proachieve.dbo.PG_ethnicGroup AS eg
    LEFT JOIN	[FECAS].Proachieve.dbo.pv_midpoint AS pv ON pv.pg_ethnicgroupid = eg.pg_ethnicgroupid
    			AND pv.pv_midpointid = @mpID
    			AND pv.pg_aggcourseid LIKE 'E2%'
    			AND pv.pg_expendyrid = '05/06'
    LEFT JOIN	[FECAS].Proachieve.dbo.GN_AggCourseStructure AS gn ON gn.pg_aggcourseid = pv.pg_aggcourseid
    GROUP BY	eg.pg_ethnicgroupname,
    		pv.pg_expendyrid
    ORDER BY	eg.pg_ethnicgroupname
    Also see http://www.sqlteam.com/article/writi...joins-in-t-sql


    [hr][font=Courier New]E 1255'05.25"
    N 5604'39.16"[/font=Courier New]

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks for posting the solution

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    look at post #5 above (and also post #8)

    then look at the solution

    tadaaaaa!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •