sql - Avoid repeatation of rows for every instance when joined with a table -

sql - Avoid repeatation of rows for every instance when joined with a table -

hi have 3 tables , trying bring together them want table. have tried grouping , temp tables options desired table no help. want avoid duplicates every instance of value in 1 table table.

table 1 client table:

cstid cstdetails csttype ---------- --------------- ------------ 1 address 1 1 2 address 2 1 3 address 3 1 4 address 4 2 5 address 5 2

table 2 client relationship:

cstid cstgroupid ---------- ---------------- 1 4 (this same customerid) 2 5 (this same customerid) 3 4 (this same customerid)

table 3 client notes:

cstid notesid notetxt ----------- --------- --------- 1 1 note11 1 2 note12 1 3 note13 3 1 note31 4 1 note41 4 2 note42 4 3 note43 4 4 note44 4 5 note45

now want result in below format

table result: (noteid) (notetxt) (noteid) (notetxt) cstid cstdetails cstgroupid cstnoteid cstnote cstgroupnoteid cstgroupnote 1 address1 4 1 note11 1 note41 1 address1 4 2 note12 2 note42 1 address1 4 3 note13 3 note43 1 address1 4 null null 4 note44 1 address1 4 null null 5 note45

but getting cstgroupnote repeated cstnote, trying avoid.

is there way accomplish result?

below code use:

select c.cstid, c.cstdetails, cn.cstnotesid, cn.cstnotetxt insert temp1 client c left outer bring together customernotes cn on c.cstid = cn.cstid c.customertypeid = 1 select cr.cstid, cr.cstgroupid, cn.cstgroupnoteid, cn.cstnotetxt insert temp2 customerrelationship cr left outer bring together customernotes cn on cr.cstgroupid = cn.customerid select t1.cstid, t1.cstdetails, t1.cstnotesid, t1.cstnotetxt, t2.cstgroupnoteid, t2.cstnotetext temp1 t1 left outer bring together t2 on t1.cstid = t2.cstid

try:

select cstid, max(cstdetails) cstdetails, max(cstgroupid) cstgroupid, max(cstnoteid) cstnoteid, max(cstnote) cstnote, max(cstgroupnoteid) cstgroupnoteid, max(cstgroupnote) cstgroupnote (select c.cstid, c.cstdetails, 0 cstgroupid, n.notesid cmbnotesid, n.notesid cstnoteid, n.notetxt cstnote, 0 cstgroupnoteid, '' cstgroupnote client c left outer bring together customernotes n on c.cstid = n.cstid c.customertypeid = 1 union select c.cstid, c.cstdetails, r.cstgroupid, n.notesid cmbnotesid, 0 cstnoteid, '' cstnote, n.notesid cstgroupnoteid, n.notetxt cstgroupnote client c left outer bring together customerrelationship r on c.cstid = r.cstid left outer bring together customernotes n on r.cstgroupid = n.cstid c.customertypeid = 1) u grouping cstid, cmbnotesid

sql sql-server-2008 tsql