| Author |
 Topic  |
|
|
fenster89411
Starting Member
United Kingdom
7 Posts |
 Posted - 02/27/2013 : 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 tables
but on the phone numbers table I need 3 different results.
The phones table has:
clientcode phoneType PhoneNumber
1 1 123
2 2 456
2 3 789
3 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:
select
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,
clientPhoneNumbers.cpnPhoneNumber as Home --Where cpnPhoneType =1
clientPhoneNumbers.cpnPhoneNumber as Mobile --Where cpnPhoneType =2
clientPhoneNumbers.cpnPhoneNumber as Work --Where cpnPhoneType =3
from clients
INNER JOIN addresses ON clients.cltcode = addresses.adrcode
INNER JOIN clientPhoneNumbers on clients.cltcode = clientPhoneNumbers.cpnClientcode
what 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.
|
Edited by - fenster89411 on 02/27/2013 10:18:50
|
|
|
singularity
Posting Yak Master
149 Posts |
Posted - 02/27/2013 : 09:10:29
|
select
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,
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 Work
from clients
INNER JOIN addresses ON clients.cltcode = addresses.adrcode
INNER JOIN clientPhoneNumbers on clients.cltcode = clientPhoneNumbers.cpnClientcode
group 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
|
 |
|
|
fenster89411
Starting Member
United Kingdom
7 Posts |
Posted - 02/27/2013 : 09:45:44
|
Thanks singularity!
This is great.
When I run it I get a
Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'as'.
Ill have a play and find out what Im doing wrong.
Thanks again for your help! |
|
|
|
James K
Flowing Fount of Yak Knowledge
1501 Posts |
Posted - 02/27/2013 : 10:00:30
|
You should not use aliases in the GROUP BY clause:.....
INNER JOIN clientPhoneNumbers on clients.cltcode = clientPhoneNumbers.cpnClientcode
group by clients.cltCode,
clients.cltClientName,
clients.cltSalutationDesc,
clients.cltFirstName,
clients.cltSurName,
clients.cltTitleDesc,
..... |
|
|
|
fenster89411
Starting Member
United Kingdom
7 Posts |
Posted - 02/27/2013 : 10:16:47
|
RESOLVED.
Working like a dream - thanks both.
Davie.
|
|
|
|
singularity
Posting Yak Master
149 Posts |
Posted - 02/27/2013 : 21:07:53
|
| My bad, forgot to take the aliases out after I copied/pasted. |
|
|
| |
Topic |
|
|
|
Multiple select and inner join question [RESOLVED]
