Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    48

    Unanswered: Fetch 1 row only in join condition

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

  5. #5
    Join Date
    Nov 2008
    Posts
    48
    ARWinner & tonkuma Thanks for the solutions

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •