Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Left Join Query - SQL

Posted on 2013-01-30
3
Medium Priority
?
405 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

670 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