Fetch 1 row only in join condition

phil72 · #1 (**permalink**) 05-15-09, 12:02

Hi,

i need to join two tables with 1 to many relation but i want only one row from second table

Headtable

Ref_no
1
2
3

Dettable

Ref_no amt col2
1 10 a
1 10 b
1 2 c
2 7 1
2 8 2
4 100 x
4 200
result expected
ref_no Amt col2
1 10 a
2 7 1
3

or

ref_no Amt col2
1 10 b
2 7 1
3

or

ref_no Amt col2
1 2 c
2 8 2
3

It does not matter which row it picks from Dettable , result set shoud be 1 to 1 relation only. i cannt using grouping or distinct as there are other rows involved.

select a.ref_no, b.amt, b.col2 from Headtable a
left join (select * from Dettable d where a.ref_no = d.ref_no fetch first 1 row only) c on a.ref_no = c.ref_no

this does not work

or something like

select a.ref_no,
(select d.amt from Dettable d where a.ref_no = d.ref_no fetch first 1 row only) ,
(select d.col2 from Dettable d where a.ref_no = d.ref_no fetch first 1 row only)
from Headtable a

here one sub query may get 10 for amt, other subquery may get c for col2
for ref_no = 1

or something like

select a.ref_no,
(select char(d.amt) || d.col2 from Dettable d where a.ref_no = d.ref_no fetch first 1 row only) from Headtable a

but this result will have concatenated row

Thanks in advance

ARWinner · #2 (**permalink**) 05-15-09, 13:25

How about something like this:

Code:

with t1 (ref_no,ord, amt,col2) as 
(select ref_no,row_number() over(partition by ref_no),amt,col2 from dettable)
select a.ref_no, b.amt, b.col2 
from Headtable as a
left outer join t1 as b on (a.ref_no = b.ref_no and b.ord = 1)

Andy

tonkuma · #3 (**permalink**) 05-15-09, 13:51

These are not tested.

Modified OP's first query:
(Changed parts are capitalized)

Code:

select a.ref_no
     , b.amt
     , b.col2
  from Headtable a
  left join
       LATERAL
     ( select *
         from Dettable d
        where a.ref_no = d.ref_no
        fetch first 1 row only
     ) B
   ON  0=0

Modified OP's second query:
(Changed parts are capitalized)

Code:

select a.ref_no
     , (select d.amt
          from Dettable d
         where a.ref_no = d.ref_no
         ORDER BY AMT, COL2
         fetch first 1 row only)
     , (select d.col2
          from Dettable d 
         where a.ref_no = d.ref_no
         ORDER BY AMT, COL2
         fetch first 1 row only) 
  from Headtable a

tonkuma · #4 (**permalink**) 05-15-09, 23:12

Not tested.

Code:

select ref_no, amt, col2
  FROM
     ( select a.ref_no
            , row_number() over(partition by a.ref_no) AS ord
            , d.amt, d.col2 
         from Headtable as a
         left outer join
              Dettable  as d
          on  d.ref_no = a.ref_no
     ) s
 WHERE ord = 1

phil72 · #5 (**permalink**) 05-18-09, 14:40

ARWinner & tonkuma Thanks for the solutions

Internet Services

Web Development

Digital Marketing

Consumer Tech