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.
| Author |
Topic |
|
Generaltao
Starting Member
8 Posts |
 Posted - 2004-09-13 : 10:55:08
|
Okay, so judging by how fast you guys solved my problem on the other page, I imagine you guys are gurus of SQL Server. Perhaps you can help me with my next problem that revolves around a select statement.Like the other problem, this still deals with the same Vehicle Leasing database. Now, the database is designed so that I have Customers, vehicles, and leases. This particular SELECT statement needs me to return the typical first and last name and phone number, but followed by a count of how many leases they have. I thought perhaps I should do an inner join, but I am not sure how to incorporate the "count()" function to return a count of any leases found.The two tables are joined by the common 'CustomerID' field.quote:
SELECT FirstName, LastName, PhoneFROM Customer c INNER JOIN Lease lON c.CustomerID = l.CustomerID
Once again, I imagine this is a simple question that you guys have seen a million times before. Even the easiest of puzzles are difficult to newcomers though, so I hope you are understanding of my plight. Thanks in advance! |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-13 : 11:02:56
|
this should help or a variation of it... :select C.*, NoOfLeases = (select count(CustomerId) from [Lease] where c.CustomerId = CustomerId)from Customers cGo with the flow & have fun! Else fight the flow  |
 |
|
|
Generaltao
Starting Member
8 Posts |
Posted - 2004-09-13 : 11:09:41
|
| Oh wow, that's great.Thank you very much for you help! |
|
|
|
|
|
|
|
|
Returning a count with a select statement