link to two fields

Hi,

I need help with the following querty. I need to select all distinct rows from Table W. At the same time, I need to lookup Table P using PropertyID
to display MyAddress if exists in Table P. (Not all records in Table W exist in Table P) Thank you very much.

SELECT      DISTINCT(W.MyID), P.MyAddress

FROM            dbo.WL W LEFT OUTER JOIN
                         dbo.PropertyFile P ON W.MyID = P.MyID 
						 LEFT OUTER JOIN
                         dbo.PropertyFile P1 ON W.PropertyId = P1.PropertyId 

Open in new window

mcrmgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
An example of your data and your expected result would be helpful.

I can't see why you are using 2 left join (P and P1) and not using P1 in your output. Or you want to join using the 2 fields at the same time?

SELECT      DISTINCT W.MyID, P.MyAddress
FROM            dbo.WL W 
LEFT JOIN dbo.PropertyFile P 
ON W.MyID = P.MyID 
and W.PropertyId = P.PropertyId 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OMC2000Commented:
Try this

SELECT      W.MyID, min(P.MyAddress)
FROM            dbo.WL W LEFT OUTER JOIN
                         dbo.PropertyFile P ON W.PropertyId = P.PropertyId 
group by W.MyID

Open in new window

0
mcrmgAuthor Commented:
this is exactly what I need . thank you
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mcrmgAuthor Commented:
@Éric Moreau,

Thank you for your help. I now have encountered another problem. With your code as following
SELECT      DISTINCT W.MyID, P.MyAddress
FROM            dbo.WL W 
LEFT JOIN dbo.PropertyFile P 
ON W.MyID = P.MyID 
and W.PropertyId = P.PropertyId 

Open in new window


it returns more records than I need if I add P.MyAddress to SELECT. But if I remove P.MyAddress from SELECT, the returned records is spot on.

Table P has multiple records that have same MyID but use PropertyId to distinguish the difference. thanks
0
Éric MoreauSenior .Net ConsultantCommented:
But since you have "and W.PropertyId = P.PropertyId " part of your query, it should filter that no?

An example of your data and your expected result would be helpful.
0
mcrmgAuthor Commented:
It was the mistake on my end. Sorry to bother you and thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.