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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple select and inner join question [RESOLVED]

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 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.


singularity
Posting Yak Master

153 Posts

Posted - 2013-02-27 : 09:10:29
[code]
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
[/code]
Go to Top of Page

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 26
Incorrect syntax near the keyword 'as'.


Ill have a play and find out what Im doing wrong.
Thanks again for your help!
Go to Top of Page

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.cpnClientcode

group by clients.cltCode,
clients.cltClientName,
clients.cltSalutationDesc,
clients.cltFirstName,
clients.cltSurName,
clients.cltTitleDesc,
.....
Go to Top of Page

fenster89411
Starting Member

7 Posts

Posted - 2013-02-27 : 10:16:47
RESOLVED.

Working like a dream - thanks both.

Davie.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -