help with the duplicate column values

[habneh]

I have tables say tbl1, tbl2, tbl3

I am joing this table to get back some recordsets

select a.col1
b.col2
c.col3
from tbl1 a inner join tbl2 b
on a.col1 = b.col2
inner join tbl3 c
on b.col1 = c.col2

the resultset will teturn


col1 col2 col3
1370 1446 aa
1370 1446 aa
216 1549 bb
1583 1549 cc
216 1549 bb
1583 1549 cc

Here is my problem I don't want col3 to be repeated, if it repeats, it should put null instead

therefore I want the recordset to be

col1 col2 col3
1370 1446 aa
1370 1446 Null
216 1549 bb
1583 1549 cc
216 1549 Null
1583 1549 Null

how can I do that

thanks

 

[habneh]

Here is a data

declare @tbl1 table
(id int,
pname varchar(100)
)

declare @tbl2 table
(id1 int,
id int,
fname varchar(100)
)

insert into @tbl1 (id, pname)
select 1, 'aa'
union all
select 2, 'bb'
union all
select 3, 'cc'

insert into @tbl2 (id1, id, fname)
select 1, 1, 'dd'
union all
select 2, 1, 'ee'
union all
select 3, 2, 'ff'
union all
select 4, 2, 'gg'
union all
select 5, 3, 'hh'
union all
select 6, 3, 'ii'

select tmp2.id1, tmp1.id, tmp1.pname
from @tbl1 tmp1 inner join @tbl2 tmp2
on tmp1.id = tmp2.id


this gives

id1 id pname
1 1 aa
2 1 aa
3 2 bb
4 2 bb
5 3 cc
6 3 cc

but pname is duplicating, I want it to be

id1 id pname
1 1 aa
2 1 Null
3 2 bb
4 2 Null
5 3 cc
6 3 Null

No problem if other columns duplicate but pname column shouldn't repeate instead it should put Null for the repeated ones

Thanks

 

[Crowley16]

when you join table 3, instead of joining it directly, use a derived table which has distinct column3 values, and use a left join to give your nulls...

--------------------
Procrastinate Now!

 

[habneh]

Thanks for your response. But I am not getting your idea

Can you try with this data

declare @tbl1 table
(id int,
pname varchar(100)
)

declare @tbl2 table
(id1 int,
id int,
fname varchar(100)
)

insert into @tbl1 (id, pname)
select 1, 'aa'
union all
select 2, 'bb'
union all
select 3, 'cc'

insert into @tbl2 (id1, id, fname)
select 1, 1, 'dd'
union all
select 2, 1, 'ee'
union all
select 3, 2, 'ff'
union all
select 4, 2, 'gg'
union all
select 5, 3, 'hh'
union all
select 6, 3, 'ii'

select tmp2.id1, tmp1.id, tmp1.pname
from @tbl1 tmp1 inner join @tbl2 tmp2
on tmp1.id = tmp2.id


this gives

id1 id pname
1 1 aa
2 1 aa
3 2 bb
4 2 bb
5 3 cc
6 3 cc

but pname is duplicating, I want it to be

id1 id pname
1 1 aa
2 1 Null
3 2 bb
4 2 Null
5 3 cc
6 3 Null

Thanks,

 

[Crowley16]

the idea is to use left join on a subSet of your table 3, if you don't know about derived tables, then try this:

create a new table with the same columns as table3, fill that table with a list of records from table3 with distinct values on column 3

then left join on this new table...

--------------------
Procrastinate Now!