Sql-server – Duplicate result when I select with distinct values from foreign key constarint

constraintforeign keygroup bysql serversql server 2014

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.
enter image description here

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

Best Answer

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.