January 6, 2009 at 11:37 am
Hi all,
I have two tables for example
table1:Class table2:Students
ClassID Name StudentID Student Name ClassID
--------- ---- --------- ------------ -------
1 Physics 1 Sam 1
2 Chemistry 1 Sam 2
3 Biology 2 Pam 1
1 Sam 3
i want the output as
ClassID Name Number_of_Students
------- ------- ------------------
1 Physics 2
2 Chemistry 1
3 Biology 0
All tried everything but nothing works here. May be this is simple but i am unable to find the solution. I would be very grateful if anyone helps me in this.
January 6, 2009 at 11:56 am
Your question looks and sounds like something from a test.
If it's a legit question, then what you're looking for is the concept of "correlated subqueries". The book "SQL Server 2005 Bible", by Paul Nielsen, has a good section on how those work and how to write them. Other books will have that data. Possibly even an online search for that term will get you enough data to learn them.
They are also called "inline subqueries", but that's a superset of the specific thing you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2009 at 12:29 pm
Show what kind of SQL you've tried that hasn't worked. Lots of us will be glad to help, but we're not doing your homework for you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 8, 2009 at 10:39 am
Hi
There are several ways to do this and it is very basic thing in writing queries.
Check out the below link, i am sure you will be able to do it on your own.
http://articles.techrepublic.com.com/5100-10878_11-6100447.html#
Thanks -- Vj
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply