How to make a 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)

How to make a query

Author

Topic

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-31 : 04:33:51

Hello,

I am doing one Subject listing for a school.
Here one student table is ther wchich contains student informatin with subjectgroupid.
This subjectgroupid is realted to subjectgroup table. From there, we can get subject
codes for that subjectgroup. And with that subject code we ahve get subject information

My table look like as follows

Student
-------

SchoolID
regNo
EngName
SClass
SubGroupID

Subject
-------

SchoolID
SubCode
Subname

SubjectGroupID
--------------

SchoolID
SubGroupID
SubCode

Now sample values are

SubjectGroup
------------
SchoolID SubGroupID SubCode

Ab0 G 01
Ab0 G 02
Ab0 G 03
Ab0 A 03
Ab0 A 04
Ab0 A 05

Subject
-------
SchoolID SubCode SubName
Ab0 01 Eng
Ab0 02 French
Ab0 03 Math
Ab0 04 Sci
Ab0 05 Soc

Student
-------

SchoolID RegNo EngName SClass SubGroupID

Ab0 111 ABC KG1 G
Ab0 222 CDE KG1 G
Ab0 333 EFG 1A G
Ab0 444 GHI 1A A

So, output should look like (when I select class as kg1 & school as ab0)

RegNo Name Eng French Math
111 ABC 01 02 03
222 CDE 01 02 03

when I select class as 1A & school as ab0

RegNo Name Eng French Math Sci Soc

333 EFG 01 02 03 - -
444 GHI - - 03 04 05

Please help me to create query to produce this output, I have tried a lot of ways but nothgn is working
Thanks
Ceema

mr_mist
Grunnio

1870 Posts

Posted - 2006-05-31 : 04:49:22

What have you tried so far? Can you show us your code and what is not working in it?

You could probably use SUM (case...) for some of it and maybe make use of some creative cross joining.

-------
Moo. :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-31 : 04:54:37

This is cross tab.

select	s.regNo, s.EngName, 
	max(case when j.Subname = 'Eng' 	then j.SubCode else '-' end) as [Eng],
	max(case when j.Subname = 'French' 	then j.SubCode else '-' end) as [French],
	max(case when j.Subname = 'Math' 	then j.SubCode else '-' end) as [Math],
	max(case when j.Subname = 'Sci' 	then j.SubCode else '-' end) as [Sci],
	max(case when j.Subname = 'Soc' 	then j.SubCode else '-' end) as [Soc]
from	Student s inner join SubjectGroup g
	on	s.SchoolID	= g.SchoolID
	and	s.SubGroupID	= g.SubGroupID
	inner join Subject j
	on	s.SchoolID	= j.SchoolID
	and	g.SubCode	= j.SubCode
group by s.regNo, s.EngName

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-31 : 05:12:38

Hello ,

I have tried this query, but null is not replacing and it's not coming rowwise. I will try the code given to me now.

Thanks
Ceema

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-31 : 05:15:48

quote:
Originally posted by ceema

Hello ,

I have tried this query, but null is not replacing and it's not coming rowwise. I will try the code given to me now.

Thanks
Ceema

What do you mean by "null is not replacing" ? Does your data contains null value ? Post the said sample data

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-31 : 05:16:27

Hello,

Sorry, failed to post the query, the query i tried earlier was

SELECT dbo.Student.SEnglishName, dbo.Student.SRegNo,(case when dbo.Subject.SubCode = Null then 0 else dbo.Subject.SubCode end) as SubCode
, (select count(Distinct(SubCode))
from subjectgroup where sgcode in(select Distinct(SubjectGroupID) from Student where Student.SchoolID='ab0'
and SClass=1 and SubjectGroupID<>'' ) ) as RepVal FROM dbo.Student left JOIN
dbo.SubjectGroup ON dbo.Student.SchoolID = dbo.SubjectGroup.SchoolID AND
dbo.Student.SubjectGroupID = dbo.SubjectGroup.SGCode left JOIN
dbo.Subject ON dbo.SubjectGroup.SchoolID = dbo.Subject.SchoolID AND
dbo.SubjectGroup.SubCode = dbo.Subject.SubCode

where Student.SchoolID='ab0' and SClass='1A'

Regards
Ceema

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-31 : 06:39:59

Hello,

I tried the query given by Mr. Khtan, now my problem is, if any of the class is not having any of the subjects, it's coming with a '-' simbol, I have to get subjects corresponding to the class only.

for example

if i will try the query for schoolid ab0 and class as kg1 then,

for me the output is coming like

RegNo Name Eng French Math Sci Soc

111 ABC 01 02 03 - -
222 CDE 01 02 03 - -

But I want it like

RegNo Name Eng French Math
111 ABC 01 02 03
222 CDE 01 02 03

I don't want those subjects to be displayed ahich is not applicable to the class.

Anyway to get this?
Thanks
Ceema

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-31 : 06:59:27

Hello,

And one more problem is there, I can't hardcode Subjectcode, any way to fetch that too dinamically?

Thanks
Ceema

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-31 : 07:29:15

quote:
Originally posted by ceema

Hello,

And one more problem is there, I can't hardcode Subjectcode, any way to fetch that too dinamically?

Thanks
Ceema

You have to use Dynamic SQL

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-31 : 07:36:21

Hello,

Thank you,I will try

Ceema

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-31 : 07:59:52

Hello,

Even if there is hardcoding, is there a way to get only applicable subjectcodes for the class as I told earlier?

Ceema

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-31 : 08:09:36

I refer you to the usual dynamic crosstab/pivot links...

http://www.sqlteam.com/item.asp?ItemID=2955
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-31 : 08:31:56

Hello Ryan Randall,

Cross tabs are having some size problem I think, do you have any other solution for this query like what Khtan sujjested? The only problem with that query is i's fetching the entire data without connectioing it to class & subjectcode.We may want to store big amount of data.

Thanks
Ceema

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources