Duplicate result when I select with distinct values from foreign key constarint

Question

I want to select values from two different tables which have one to many relationship between them. During select it's retrieving duplicate values even when I use distinct and group by. Above table is person table and the beneath one is address.

I am using this query to retrieve matched records. I want to group address into single record but its working am I missing something? I want my result to look like this

Answer 1

Here is an example of how that could be achieved

--demo setup
Declare @Table1 table (id int, name varchar(20), lastname varchar(20), imageshortpath varchar(100), imagefullpath varchar(100))
insert into @Table1(id, name, lastname, imageshortpath, imagefullpath) values
(2108,'mudasir', 'khan','imageshortpath','imagefullpath')

Declare @Table2 table (id int, peopleid int, address varchar(20))
insert into @Table2(id, peopleid,address) values 
(1,2108,'hyderabad'),(2,2108,'larkana')

--solution
select t1.*, 
STUFF((
    SELECT ',' + address
    FROM @Table2 t2
    WHERE t2.peopleid = t1.id
    FOR XML PATH('')
    ), 1, 1, '') AS Address
from 
@Table1 t1

---

| id   | name    | lastname | imageshortpath | imagefullpath | Address           |
|------|---------|----------|----------------|---------------|-------------------|
| 2108 | mudasir | khan     | imageshortpath | imagefullpath | hyderabad,larkana |

---

Check out this excellent answer about How Stuff and 'For Xml Path' work in Sql Server.

Answer 2

This is a sort of add-on to Scott's answer. I think one of the problems here is also a misunderstanding of the DISTINCT keyword. The rows you're getting aren't duplicates, they are in fact distinct.

Even if two inputs have 9 out of 10 rows exactly the same, DISTINCT will still show both records, because that last value is still different.

Using your tables, here's a simple example:

Lets say you have only one person with the name "John Wick", and he lived in New York for some time, then later he moved to California. You now have two rows in the Address table with John Wick's FK, but with two different addresses, making each row a unique entry

So even though you typed in DISTINCT, it serves no purpose because both rows are unique and not similar.

If you remove the "City" column from the select (which was the reason each record was unique), you get the normal DISTINCT result you expect: