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 |
fenster89411
Starting Member
7 Posts |
Posted - 2013-02-27 : 08:42:16
|
Hi Guys,I am wrestling with a select statement and hope someone can help me over this bump...I need to select from clients ,addresses and phone number tablesbut on the phone numbers table I need 3 different results.The phones table has:clientcode phoneType PhoneNumber1 1 1232 2 4562 3 7893 3 10 so the client number can have up to 3 types (1 home,2 mobile,3 work)I know this select is really wrong but don't know the correct way to retrieve:selectclients.cltCode as RecordNumber,clients.cltClientName as FullName,clients.cltSalutationDesc as Salutation,clients.cltFirstName as Forename,clients.cltSurName as Surname,clients.cltTitleDesc as Title,clients.cltGender as Gender, clients.cltOrganizationDesc as Organisation,addresses.adrAddress1 as Address1,addresses.adrAddress2 as Address2,addresses.adrAddress3 as Address3,addresses.adrCityDesc as City,addresses.adrStateDesc as County,addresses.adrZipCode as PostalCode,addresses.adrCountryDesc as Country,clients.cltEmail as Email,clientPhoneNumbers.cpnPhoneNumber as Home --Where cpnPhoneType =1clientPhoneNumbers.cpnPhoneNumber as Mobile --Where cpnPhoneType =2clientPhoneNumbers.cpnPhoneNumber as Work --Where cpnPhoneType =3from clients INNER JOIN addresses ON clients.cltcode = addresses.adrcodeINNER JOIN clientPhoneNumbers on clients.cltcode = clientPhoneNumbers.cpnClientcodewhat I want is to be able to have phone 1, phone 2, phone 3 in the output separately.I have a feeling this is WAY beyond my basic knowledge...And any advice is really welcome.Thanks,Davie. |
|
singularity
Posting Yak Master
153 Posts |
Posted - 2013-02-27 : 09:10:29
|
[code]selectclients.cltCode as RecordNumber,clients.cltClientName as FullName,clients.cltSalutationDesc as Salutation,clients.cltFirstName as Forename,clients.cltSurName as Surname,clients.cltTitleDesc as Title,clients.cltGender as Gender, clients.cltOrganizationDesc as Organisation,addresses.adrAddress1 as Address1,addresses.adrAddress2 as Address2,addresses.adrAddress3 as Address3,addresses.adrCityDesc as City,addresses.adrStateDesc as County,addresses.adrZipCode as PostalCode,addresses.adrCountryDesc as Country,clients.cltEmail as Email,max(case when cpnPhoneType = 1 then clientPhoneNumbers.cpnPhoneNumber end) as Home,max(case when cpnPhoneType = 2 then clientPhoneNumbers.cpnPhoneNumber end) as Mobile,max(case when cpnPhoneType = 3 then clientPhoneNumbers.cpnPhoneNumber end) as Workfrom clientsINNER JOIN addresses ON clients.cltcode = addresses.adrcodeINNER JOIN clientPhoneNumbers on clients.cltcode = clientPhoneNumbers.cpnClientcodegroup by clients.cltCode as RecordNumber,clients.cltClientName as FullName,clients.cltSalutationDesc as Salutation,clients.cltFirstName as Forename,clients.cltSurName as Surname,clients.cltTitleDesc as Title,clients.cltGender as Gender, clients.cltOrganizationDesc as Organisation,addresses.adrAddress1 as Address1,addresses.adrAddress2 as Address2,addresses.adrAddress3 as Address3,addresses.adrCityDesc as City,addresses.adrStateDesc as County,addresses.adrZipCode as PostalCode,addresses.adrCountryDesc as Country,clients.cltEmail as Email[/code] |
|
|
fenster89411
Starting Member
7 Posts |
Posted - 2013-02-27 : 09:45:44
|
Thanks singularity!This is great.When I run it I get a Msg 156, Level 15, State 1, Line 26Incorrect syntax near the keyword 'as'. Ill have a play and find out what Im doing wrong.Thanks again for your help! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-27 : 10:00:30
|
You should not use aliases in the GROUP BY clause:.....INNER JOIN clientPhoneNumbers on clients.cltcode = clientPhoneNumbers.cpnClientcodegroup by clients.cltCode,clients.cltClientName,clients.cltSalutationDesc,clients.cltFirstName,clients.cltSurName,clients.cltTitleDesc,..... |
|
|
fenster89411
Starting Member
7 Posts |
Posted - 2013-02-27 : 10:16:47
|
RESOLVED.Working like a dream - thanks both.Davie. |
|
|
singularity
Posting Yak Master
153 Posts |
Posted - 2013-02-27 : 21:07:53
|
My bad, forgot to take the aliases out after I copied/pasted. |
|
|
|
|
|
|
|