CODEWITHSUNDEEP
sqlself-join
user9073369
user9073369

Reputation:

SQL query with two self-joins - is there a better way

could you help with this query please.

Table structure is as folllows:

PropertyOwner

Property

Now, if I have a LoanId, how can I find all properties of property ownerId's who have taken the given LoanId?

I am having the below now but it looks awkward:

Select po.OwnerId, po.PropertyId
from Property
join PropertyOwner po on po.PropertyId= Property.PropertyId
join PropertyOwner po2 on po2.OwnerId = po.OwnerId 
join Property pp on po2.PropertyId= pp.PropertyId and pp.LoanId = @_givenLoanId

Is there a better way?

Upvotes: 0

Views: 50

Answers (2)

Niharika Bitra
Niharika Bitra

Reputation: 477

This is what you're looking for:

SELECT OwnerId, PropertyId
FROM PropertyOwner
WHERE OwnerId IN 
(
    SELECT DISTINCT o.OwnerId
    FROM Property p 
    JOIN PropertyOwner o
        ON o.PropertyId = p.PropertyId
    WHERE LoanId = @_givenLoanId
);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270341

Exists comes to mind as being a more direct interpretation of what you are doing:

Select po.OwnerId, po.PropertyId
from PropertyOwner po
where exists (select 1
              from Property p2 join
                   PropertyOwner po2
                   on p2.PropertyId = po2.PropertyId
              where po2.OwnerId = po.OwnerId and
                    p2.LoanId = @_givenLoanId
             );

Upvotes: 1

Related Questions