Solved

Left Join Query - SQL

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

786 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