SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple select and inner join question [RESOLVED]
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fenster89411
Starting Member

United Kingdom
7 Posts

Posted - 02/27/2013 :  08:42:16  Show Profile  Reply with Quote
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

151 Posts

Posted - 02/27/2013 :  09:10:29  Show Profile  Reply with Quote

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

fenster89411
Starting Member

United Kingdom
7 Posts

Posted - 02/27/2013 :  09:45:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3334 Posts

Posted - 02/27/2013 :  10:00:30  Show Profile  Reply with Quote
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

United Kingdom
7 Posts

Posted - 02/27/2013 :  10:16:47  Show Profile  Reply with Quote
RESOLVED.

Working like a dream - thanks both.

Davie.
Go to Top of Page

singularity
Posting Yak Master

151 Posts

Posted - 02/27/2013 :  21:07:53  Show Profile  Reply with Quote
My bad, forgot to take the aliases out after I copied/pasted.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000