Select number of occurrences from join table 2

[Westond]

Here is my table structure:

StudentID First Last
1 John Doe
2 Jane Doe

ClassID StudentID Instructor Time
1 1 fsd 11:00
2 1 asf 12:00
3 2 jkl 12:00

I would like to have a query that gets all the info in the first table and gets the number of classes in the second table I think I am making this too complicated hehe.

results I want would be like

StudentID First Last NumClasses
1 John Doe 2
2 Jane Doe 1

The end product is more complex than this, I just need to get that info in 1 query.

THANKS!

 

[SQLDenis]

Why does this sound like homework?

lookup Left Join, Correlated Subqueries and Count in Books on Line

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Westond]

haha no this is not homework. I know how to join tables that is no problem, I dont want all the info from the second table and I dont want to have to use 2 queries to get the info.

Select info from table1
where..

select count(StudentID) From table2
where StudientID = 1

I want all the data together so when I bind to a gridview I dont have to write code in the gv to handle it. I am looking for the most efficent way to write the query, I already know how to make it work.

I made the example sound as simple as possible to get the point across the whole student this is fictitious.

 

[gmmastros]

Not tested....

Select StudentId,
       First,
       Last,
       Count(ClassTable.ClassId) As ClassCount
From   StudentTable
       Left Join ClassTable
         On StudentTable.StudentId = ClassTable.StudentId
Group By StudentTable.StudentId, 
       StudentTable.First, 
       StudentTable.Last

Also... I didn't know the table names so I improvised.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

2 ways (untested of course)

Select t1.*,coalesce(t2.StudentCount,0) as StudentCount from
(select count(StudentID)as StudentCount,StudentID From table2
group by StudentID) t2 left join table1 t1 on t2.StudentID = t1.StudentID

Select t1.*, (select count(StudentID)from table2 t2 where t2.StudentID = t1.StudentID) As StudentCount
from table1


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Westond]

Thanks guys I see how it works now : ). I have only used Correlated Subqueries in where clause mainly using IN. Didn't know it works in the Select like that.

thanks

 

[Westond]

1 more question. This query will work but the Status I am looking for is contained in a lookup table called User_Status. Is there a way I can use the values in the Lookup Table and not have to hard code them in to the query like this?

Select t1.*,
(select count(Training_ID)from Train_TrainingsTaken t2 where t2.Status = 'Approved') As StudentApproved,
(select count(Training_ID)from Train_TrainingsTaken t2 where t2.Status = 'Requested') As StudentRequested,
(select count(Training_ID)from Train_TrainingsTaken t2 where t2.Status = 'Declined') As StudentDeclined
from Train_TrainingCourses t1