link to two fields

mcrmg
mcrmg used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Consultant
Top Expert 2016
Commented:
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

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

Author

Commented:
this is exactly what I need . thank you
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
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.

Author

Commented:
It was the mistake on my end. Sorry to bother you and thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial