Click here to Skip to main content
14,926,224 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
HI all,

I have three tables
1. Organisation table
2. Class table
2. Student Table

Columns in organisation table are

Id, orgname, orgAddress

Columns in class table are

Id, Orgid,Classname
Note(Id column of orgtable is fk for Orgid in class table)

Columns in student table are

Id,ClassId,StudentName,Address,PhoneNo
Note(Id column of class table is fk for ClassId in student table)


So my requirement is i need complete organisation details along with student count for each organisation

Output:
Slno, orgname, orgAddress, student count
1 pvrInstitute Banglore 30
2 nskInstitute naagpur 50

What I have tried:

HI all,

I have three tables
1. Organisation table
2. Class table
2. Student Table

Columns in organisation table are

Id, orgname, orgAddress

Columns in class table are

Id, Orgid,Classname
Note(Id column of orgtable is fk for Orgid in class table)

Columns in student table are

Id,ClassId,StudentName,Address,PhoneNo
Note(Id column of class table is fk for ClassId in student table)


So my requirement is i need complete organisation details along with student count for each organisation

Output:
Slno, orgname, orgAddress, student count
1 pvrInstitute Banglore 30
2 nskInstitute naagpur 50
Posted
Updated 22-Feb-16 13:13pm
v2

Use a JOIN: SQL Joins[^]
This is your homework, so I'll not give you the code. Just read the article, and see how it works - it's not complex, so follow the example with yoru DB and you should be fine.
   
Comments
kalyan10qwerty 22-Feb-16 4:22am
   
Hi OriginalGriff

I have tried half query
select count(*) from Students s,Classes c,Organisations o where s.ClassId=c.Id and c.OrganisationId=o.Id

now i need orgname which is in orgtable along with the above query so please help me
OriginalGriff 22-Feb-16 4:31am
   
Yes - that's why you want a JOIN.
Follow the link - it's not difficult to do!
kalyan10qwerty 22-Feb-16 4:33am
   
yes your right. but i am unable to write complete query even though after referring to the information provided by you. so give me the solution
OriginalGriff 22-Feb-16 4:50am
   
What did you try after reading it?
kalyan10qwerty 22-Feb-16 4:56am
   
select o.organisationcode, o.organisationname, ( select count(*) from Students s,Classes c,Organisations o where s.ClassId=c.Id and c.OrganisationId=o.Id ) as StudentCount from Organisations o,Students s,Classes c
where s.ClassId=c.Id and c.OrganisationId=o.Id

In studentCount it is retrieving all organisation students count in-stud of that particular org student count :(. Some where i am missing the condition. Unable to figure it out. So help me
OriginalGriff 22-Feb-16 5:09am
   
So...you didn't follow the link, you didn't read it, and you didn't try a JOIN then.
Is there any point in my trying to help you if you ignore everything I suggest?
kalyan10qwerty 22-Feb-16 5:11am
   
select count(*) from Students s, Organisations o, Classes c
where s.ClassId=c.Id and c.OrganisationId=o.Id and o.OrganisationCode='204050'

above is the query for which i got student count as i specified the particular organisation code.
I have around 800 org so please give me the solution
OriginalGriff 22-Feb-16 5:16am
   
Read the link: this is your homework, so you need to learn this stuff. Your tutor wants your solution, not mine!
Seriously, this isn't complicated...
I got the solution

C#
SELECT o.Id, o.OrganisationCode, o.OrganisationName, count(*) As StudentCount
FROM Students S
INNER JOIN Classes C on s.ClassId = C.Id 
INNER JOIN Organisations O on c.OrganisationId = o.Id
GROUP BY o.Id, o.OrganisationCode, o.OrganisationName
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900