select p.polnum
       ,p.code
       ,n.custname 
from   @pol p
left join (select rela_key, min(nameid) as nameid from @rel group by rela_key)
       r on r.rela_key = p.code + p.polnum + 'IN'
left join @name n 
       on n.nameid = r.nameid

select p.polnum
       ,p.code
       ,oa.custname 
from   @pol p
outer apply (
       select top 1 n.custname
       from   @rel r
       inner join @name n
              on n.nameid = r.nameid
       where  r.rela_key = p.code + p.polnum + 'IN'
       order by r.nameid
       ) oa

quote:

---

Originally posted by vijayisonly

Hi,
I have the follwing table structure

declare @pol table (polnum varchar(8),code varchar(2))
insert @pol
select 'L1234567','02'

declare @rel table (rela_key varchar(20),nameid int)
insert @rel
select '02L1234567IN',1 union all
select '02L1234567IN',2

declare @name table (nameid int,custname varchar(20))
insert @name
select 1,'name1' union all
select 2,'name2'

And this is my query

select p.polnum,p.code,n.custname from @pol p
left join @rel r on r.rela_key = p.code + p.polnum + 'IN'
left join @name n on n.nameid = r.nameid

As you might have guessed, it returns two rows for name1 and name2.

The problem here is, the @rel table has 2 rela_key's with the same value. This is an invalid scenario. This duplicate record comes in through the system through some erroneous online processing, which we are not able to figure out yet.

This SQL is part of a report, so the same policy number appears twice in the report which causes some confusion. Can we somehow alter the query so that the JOIN wud return only one row and leave out the other. It doesn't matter which one is being ignored.

---