We help IT Professionals succeed at work.

Slightly complicated select statement

Hello SQL Gurus...

I'm pretty sure this is a quick one, but can't for the life of me remember how to do it..

I'm trying to do a select from 3 tables -

dbo.Member (Parent)
dbo.[Member-Phone]
dbo.[Member-Phone]

Select from dbo>member is fine, however as the member can have multiple phone numbers and multiple email addresses...

I can do that fine also..

my code so far is --

SELECT 
M.ID,
M.FirstName, 
M.LastName, 
M.ClubName, 
M.[UK-Member],
M.[Eire-Member],
M.[EU-Member],
M.AccountType, 
M.Birthdate, 
M.Startdate, 
M.URL, 
M.ExitDate,
E.Email,
P.Phone
FROM dbo.Member M
inner join dbo.[Member-Email] E
on E.MemberID = M.ID
inner join dbo.[Member-Phone] P
on P.MemberID = M.ID
LEFT OUTER join dbo.[Member-Phone] MP
on MP.MemberID = M.ID
WHERE M.ID = 12 AND E.defaultemail = 'Y' AND P.DefaultP = 'Y'

Open in new window


Where it is getting complicated is around here --

LEFT OUTER join dbo.[Member-Phone] MP
on MP.MemberID = M.ID

Open in new window


In that what I want to do here is select the top 1 result from dbo.[Member-Phone]  where [MemberID] = dbo.Member.ID AND [Type] = 2 (Mobile).

Taking into consideration that there may not be a record associated, but the select statement should still return results, just with nothing in the MP.Phone colun that will be added to the select statement under P.Phone...

SELECT 
M.ID,
M.FirstName, 
M.LastName, 
M.ClubName, 
M.[UK-Member],
M.[Eire-Member],
M.[EU-Member],
M.AccountType, 
M.Birthdate, 
M.Startdate, 
M.URL, 
M.ExitDate,
E.Email,
P.Phone
MP.Phone  <-HERE

Open in new window


Hope that makes sense?

Thanks
Comment
Watch Question

Commented:
If you want 2 rows returned when there are 2 mobile phones or there is always only one mobile phone then just adding the
 AND MP.[Type] = 2
at the end of your WHERE is enough

When you only want max 1 mobile phone and there could be multiple then

SELECT 
M.ID,
M.FirstName, 
M.LastName, 
M.ClubName, 
M.[UK-Member],
M.[Eire-Member],
M.[EU-Member],
M.AccountType, 
M.Birthdate, 
M.Startdate, 
M.URL, 
M.ExitDate,
E.Email,
P.Phone,
(select top 1 mp.Phone 
 from dbo.[Member-Phone] MP
 WHERE MP.MemberID = M.ID and mp.type = 2
 ) as Mobile
FROM dbo.Member M
inner join dbo.[Member-Email] E
on E.MemberID = M.ID
inner join dbo.[Member-Phone] P
on P.MemberID = M.ID
WHERE M.ID = 12 AND E.defaultemail = 'Y' AND P.DefaultP = 'Y'

Open in new window

Author

Commented:
Great thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.