Solved

Left Join Query - SQL

Posted on 2013-01-30
3
379 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
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.

910 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

23 Experts available now in Live!

Get 1:1 Help Now