CODEWITHSUNDEEP
sqlsql-serverjoinstored-proceduresleft-join
Hemant Dhakal
Hemant Dhakal

Reputation: 1

SQL Join filter

I want to get single row after join as result, where table A can have columns like ID,Name,Age,Surname e.t.c and table B have ID,address e.t.c a person can have multiple addresses and if i use where condition get person from address i am getting multiple rows for the same person which want as single for single person i don't need all the addresses in my result.

SELECT * FROM A LEFT JOIN B ON A.ID = B.ID
where b.address=add1 or/and b.address=add2

This results same person in different rows with multiple address which causing me redundant data. Can someone please suggest a better way here...!

Upvotes: 0

Views: 48

Answers (1)

Srinivasan Rajasekaran
Srinivasan Rajasekaran

Reputation: 585

By Window Function you can achieve it,

SELECT * FROM (
SELECT *,ROW_NUMBER() OVER (PARTITION BY P.PersonId ORDER BY P.PersonId) AS RowNum
FROM Persons P
LEFT JOIN Address A ON A.PersonId = P.PersonId    ) A
WHERE RowNum = 1

Upvotes: 1

Related Questions