Solved

Left Join Query - SQL

Posted on 2013-01-30
3
393 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 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