Solved

Left Join Query - SQL

Posted on 2013-01-30
3
374 Views
Last Modified: 2013-01-30
Hello Experts,

I'm confused! I'm building a query that queries multiple tables --

SELECT M.[ID],
CASE WHEN [title] = 1 THEN 'Mr'
WHEN [title] = 2 THEN 'Mrs'
WHEN [title] = 3 THEN 'Miss'
WHEN [title] = 4 THEN 'Ms'
ELSE '' END AS Title,
[FirstName],
[LastName],
[Birthdate],
[ClubName] as TradingAs,
[AccountType],
CASE 
WHEN [UK-Member] = 'Y' THEN 'UK'
WHEN [Eire-Member] = 'Y' THEN 'Eire'
WHEN [EU-Member] = 'Y' THEN 'EU' END AS PartnerCategory,
[RDA-ID],
[PGA-Number],
EX.[Exchequer-Code],
[Startdate],
[ExitDate],
[Profits],
[VAT-Liable],
P.Phone,
'Mobile',
'TGIEmail',
'AltEmail',
[URL],
PS.facebook,
PS.twitter,
[RetainerSurvey]
FROM [dbo].[Member] M
Left join dbo.[Member-Exchequer-Codes] EX on EX.MemberID = M.ID
left join dbo.PartnerSocialMedia PS on PS.PartnerID = M.ID
left join (select top (1) P.MemberID, P.Phone from dbo.[Member-Phone] P Where Type = 1 AND DefaultP = 'Y' Group by P.MemberID) P on P.MemberID = M.ID

Open in new window


My query is with this section --

left join (select top (1) P.MemberID, P.Phone from dbo.[Member-Phone] P Where Type = 1 AND DefaultP = 'Y' Group by P.MemberID) P on P.MemberID = M.ID

Open in new window


So the thing here is -

each member can have multiple (or no) records in dbo.[Member-Phone] but for the sake of this query I am looking to left join from a subset of results where Type = 1 AND DefaultP = 'Y'

Which should give me 1 or no record for each member.....

However when I run this query I get --

Msg 8120, Level 16, State 1, Line 35
Column 'dbo.Member-Phone.Phone' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What's wrong with my logic here?

Thank you
0
Comment
Question by:garethtnash
3 Comments
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 500 total points
ID: 38835693
You just need to add p.phone to the group by clause :

left join (select top (1) P.MemberID, P.Phone from dbo.[Member-Phone] P Where Type = 1 AND DefaultP = 'Y' Group by P.MemberID, P.Phone) P on P.MemberID = M.ID 

Open in new window

0
 

Author Closing Comment

by:garethtnash
ID: 38835714
Thank you
0
 
LVL 15

Expert Comment

by:tim_cs
ID: 38835715
If only one record can be returned and you are using TOP 1 then I don't see a reason to even have the group by.  You can just remove it completely from that subquery.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now