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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-05-23 : 08:55:16
|
| Eric writes "I am a ColdFusion developer and I got a question from a recruiter that goes beyond my SQL skills. It's pretty non-db specific. We have 3 tables... cli_client, cnt_contact,cnt_xref (mapping table betweent he 2 containing contact_d and cli_number). cli_client contains client info...primary key is cli_number. cnt_contact contacts the clients contacts...primary key is contact_id. The question given to me is how would i extract the client that has the most contacts. Most of the queries i do are the basic ones to pull data to populate pages...anything higher than that and I usally go pay the DBA a visit ;-) Any help on this would be appreciated. here's a listing of the tables and the columns...cli_clientcli_name, cli_number, address, city, state, zip....cnt_contactcontact_id,company, address, city, state, zipcnt_xfercli_number, contact_idThanks in advance..." |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-23 : 09:14:36
|
| [code]SELECT TOP 1 cli_number, COUNT(*) AS [T_Count]FROM cli_client AS C JOIN cnt_xfer AS CX ON CX.cli_number = C.cli_number JOIN cnt_contact AS CON ON CON.contact_id = CX.contact_idGROUP BY cli_numberORDER BY T_Count DESC[/code]Kristen |
 |
|
|
|
|
|
|
|
Stumped by a recruiter question...