Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33

    Unanswered: Query multiple tables

    Hello all,

    I have 2 tables

    1.
    CREATE TABLE "MAINORDTB"
    (
    "MAIN_ID" NUMBER(10,0) NOT NULL,
    "MAIN_ORDNAME" VARCHAR2(25) NOT NULL,
    "MAIN_STAT" VARCHAR2(100) NOT NULL,
    "MAIN_SUBID" NUMBER(10,0))

    MAIN_ID MAIN_ORDNAME MAIN_STAT MAIN_SUBID
    001 "Order name1" "Status1" 001
    002 "Order name2" "Status2" 002
    003 "Order name3" "Status3" 002
    004 "Order name4" "Status4" 002
    005 "Order name1" "Status5" 005


    MAIN_SUBID is the foreign key to MAINID
    in the same table MAINORDTB (Parent child
    relation)

    For one MAIN_ID this table will have many records,
    with MAIN_SUBID having reference to the first entry
    of MAIN_ID.

    2.
    CREATE TABLE "DETAILORDTB"
    (
    "DETAIL_ID" NUMBER(10,0) NOT NULL,
    "MAIN_SUBID" NUMBER(10,0),
    "DETAILORDTEXT" VARCHAR2(25) NOT NULL,
    "DETAILORDSTAT" NUMBER(1,0),
    "DETAILORDDATE" DATE)

    DETAIL_ID MAIN_SUBID DETAILORDTEXT DETAILORDSTAT DETAILORDDATE
    001 002 "Text1 "Status1" 11/01/2004
    002 002 "Text2 "Status2" 15/01/2004
    003 002 "Text3 "Status3"
    004 002 "Text4 "Status4"

    MAIN_SUBID refers to the MAINORDTB.MAIN_SUBID.
    DETAIL_ID is a running internal id.
    For each of the MAIN_SUBID there will be many
    records(Lines) in this DETAILORDTB table.
    DETAILORDDATE field is filled by the user
    when the line entry is closed.


    1. I want a query which will list only the
    last (or latest) row for each of the
    MAINORDTB.MAIN_SUBID. The same query should
    also list the DETAILORDTB.DETAILORDTEXT for the next due
    line (That is if there 3 lines, and in this
    3 lines, if 1st line the user has entered the DETAILORDDATE means
    it should list the DETAILORDTEXT for the 2nd
    line(record)).

    The query should display the details as

    MAIN_ID MAIN_ORDNAME MAIN_STAT MAIN_SUBID DETAILORDTEXT
    001 "Order name1" "Status1" 001
    004 "Order name4" "Status4" 002 "Status3"
    005 "Order name1" "Status5" 005



    thank you.

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Talk about weird designs..

    Anyways,
    Code:
    SQL@9iR2> select * from mainordtb;
    
       MAIN_ID MAIN_ORDNAME    MAIN_STAT  MAIN_SUBID
    ---------- --------------- ---------- ----------
             1 Order name1     Status1             1
             2 Order name2     Status2             2
             3 Order name3     Status3             2
             4 Order name4     Status4             2
             5 Order name1     Status5             5
    
    SQL@9iR2> select * from detailordtb;
    
     DETAIL_ID MAIN_SUBID DETAILO DETAILORDSTAT DETAILORD
    ---------- ---------- ------- ------------- ---------
             2          2 Text1               1 11-JAN-04
             2          2 Text2               2 15-JAN-04
             2          2 Text3               3
             2          2 Text4               4
    
    SQL@9iR2> select max(main_id) main_id,
      2         max(main_ordname) main_ordname,
      3         max(main_stat) main_stat,
      4         max(main_subid) main_subid,
      5         max(maxsdet) detailordtext
      6    from (
      7  select main_id,
      8         main_ordname,
      9         main_stat,
     10         main_subid,
     11         case when maxdate = detailorddate
     12              then lead(detailordtext) over(order by rownum)
     13          end maxsdet
     14    from (
     15  select m.main_id,
     16         m.main_ordname,
     17         m.main_stat,
     18         m.main_subid,
     19         d.detailorddate,
     20         d.detailordtext,
     21         max(d.detailorddate) over(partition by m.main_subid) maxdate
     22    from mainordtb m, detailordtb d
     23   where m.main_subid = d.main_subid(+)
     24         )
     25         )
     26   group by main_subid
     27  /
    
       MAIN_ID MAIN_ORDNAME    MAIN_STAT  MAIN_SUBID DETAILO
    ---------- --------------- ---------- ---------- -------
             1 Order name1     Status1             1
             4 Order name4     Status4             2 Text3
             5 Order name1     Status5             5
    
    SQL@9iR2>

  3. #3
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33
    Thnk you very much it worked.

Posting Permissions

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