Problem with query - 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)

Problem with query

Author

Topic

5fifty5
Starting Member

35 Posts

Posted - 2008-02-08 : 04:01:29

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.


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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 04:08:03

quote:
Originally posted by 5fifty5

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.

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 (gn.pg_aggcourseid LIKE 'E2%' or gn.pg_aggcourseid IS NULL)
group by
eg.pg_ethnicgroupname,
pv.pg_expendyrid
order by
eg.pg_ethnicgroupname

change like this and see

5fifty5
Starting Member

35 Posts

Posted - 2008-02-08 : 04:17:42

Doesn't work.

I have also tried all sorts of joins but as soon as I put in the where clause for aggcourseid, it wont show the records as null.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 04:31:39

I see now. You have to take PG_ethnicGroup as base table and take others as left join with it if you want to get EthnicCity always.
ie.
SELECT *
from
[FECAS].Proachieve.dbo.PG_ethnicGroup
LEFT JOIN [FECAS].Proachieve.dbo.pv_midpoint pv
LEFT JOIN [FECAS].Proachieve.dbo.GN_AggCourseStructure
WHERE....

please post table strucutures and saple data if you want detailed soln

5fifty5
Starting Member

35 Posts

Posted - 2008-02-08 : 06:11:06

Well, as you can see I am performaing calculations on data which does not exist in ethnicgroup, I wont be able to use it as primary table and join it with others (as far as my understanding goes)

Just to let you know, EthnicGroup is a view and NOT a table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 07:17:12

quote:
Originally posted by visakh16

quote:
Originally posted by 5fifty5

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.

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
right join [FECAS].Proachieve.dbo.PG_ethnicGroup eg on pv.pg_ethnicgroupid = eg.pg_ethnicgroupid

where pv_midpointid = @mpid
and pg_expendyrid = '05/06'

and (gn.pg_aggcourseid LIKE 'E2%' or gn.pg_aggcourseid IS NULL)
group by
eg.pg_ethnicgroupname,
pv.pg_expendyrid
order by
eg.pg_ethnicgroupname

change like this and see

did you try this?

5fifty5
Starting Member

35 Posts

Posted - 2008-02-08 : 07:21:00

Indeed. As I said earlier, I have tried all the joins and now done with this condition as well.

Same result.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 13:09:44

i need some sample data from your tables and your expected result out of them too. Can you post the same please?

5fifty5
Starting Member

35 Posts

Posted - 2008-02-11 : 04:03:13

This is the output withOUT the aggcourseid condition

This is the output when i put in the aggcourseid condition

You can see 2 rows are missing when I've put in the condition.

I have looked around and haven't found a way with which I can attach the sample data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 04:09:39

Thanks for this. But this doesnt help much. WHat i asked was the data from source tables. Atleast data for those cases which were excluded in final result.(2 rows)

5fifty5
Starting Member

35 Posts

Posted - 2008-02-11 : 05:01:05

well i reallya ppreciate your help in this but I am unable to find a way which I can use to attach sample data on this forum. Table has around 30+ fields and the data would be messed up if I post it like that over here...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 05:08:43

Just choose results to text in query analyser and copy and paste the message window here using code format.

5fifty5
Starting Member

35 Posts

Posted - 2008-02-11 : 05:51:39

I hope this helps. Just selected few colums which were of use and excluded the rest.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 06:59:43

Hi i was asking you to give the data for these two records from source tables (Proachieve.dbo.pv_midpoint pv,[FECAS].Proachieve.dbo.GN_AggCourseStructure &
[FECAS].Proachieve.dbo.PG_ethnicGroup )

5 Bangladeshi 49.0 61.223999 87.754997 69.766998
5 Black Other 85.0 68.235001 83.528999 81.690002
this was to check how its joining on and being excluded from result.

5fifty5
Starting Member

35 Posts

Posted - 2008-02-11 : 07:35:52

I think you misunderstood the problem altogether then. There is no data for that ethnicity. All I want to do is put in null and that is why there is a join to EthnicGroup table so that I can have all the ethnicities but that is not working. Its not bringing up the data where is there is no related records. Where as I want it to show all ethnicities even if they have got no data/records.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 08:49:41

In that case i'm sure that a left join with ethic related table will bring you all the data from the other tables.ANd in the filter you just need to include null condition also.Are you sure your filter conditions are all correct? which of these come from other two tables (one after joins)

where pv_midpointid = @mpid
and (pg_expendyrid = '05/06' or pg_expendyrid is null)

and (gn.pg_aggcourseid LIKE 'E2%' or gn.pg_aggcourseid IS NULL)

5fifty5
Starting Member

35 Posts

Posted - 2008-02-11 : 09:09:10

I am afraid it does not bring up the record. I never usually post stuff on forums but this one is weird. Its just not making sense to me that why would it NOT show the records even with a left join or whichever join I use.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 09:16:15

it certainly lies in your join or filter condition . But to confirm i really need data from those three tables and what you really want out of them (even a single example of case which is not working will do the trick) which i havent got till now.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 09:26:34

And this could go on FOREVER...
555 need to READ and FOLLOW this advice found on this blog post
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

pootle_flump

1064 Posts

Posted - 2008-02-11 : 10:01:17

http://www.dbforums.com/showthread.php?p=6321037#post6321037

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 10:26:48

[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[/code]
Also see http://www.sqlteam.com/article/writing-outer-joins-in-t-sql

E 12°55'05.25"
N 56°04'39.16"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources