Click here to Skip to main content
15,900,589 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.
 
Share this answer
 
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
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
 
Share this answer
 

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