Results 1 to 10 of 10
  1. #1
    Join Date
    May 2002
    Posts
    62

    Unanswered: Inner join on one table

    Hi,

    I am trying to retrieve some data from one table using an inner join and want to achieve the result without writing a stored procedure.

    Here is how the result should look like:

    id cdate type wtn ref_wtn
    1 9/12/04 T 123 3499
    3 9/12/04 F 123 3499
    ----
    ----

    I tried with the following query.

    select a.ID, to_char(a.cdate,'mm/dd/yyy'), a.TYPE, a.wtn, a.REF_WTN
    from aud_sord_trx a, aud_sord_trx b
    where trunc(a.cdate) = trunc(b.cdate)
    and a.wtn = b.wtn
    and a.ref_wtn = b.ref_wtn
    and a.TYPE in ('T','F')
    and b.TYPE in ('T','F')
    order by a.ID

    But it did not work. The result was:

    id cdate type wtn ref_wtn
    1 9/12/04 T 123 3499
    2 9/12/04 F 123 3456
    3 9/12/04 F 123 3499
    4 9/12/04 T 122 3499
    ----
    ----

    I did not expect the rows 2 and 4 in the above.

    Can any one help me with this query. Looks simple to me. Am I missing something here ?

    Thanks for any help.
    -Bheemsen

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You didn't give enought information. If it isnt too large, can you give the values of cdate, wtn, ref_wtn, TYPE from the two tables.

    Until then, try

    select a.ID, to_char(a.cdate,'mm/dd/yyy'), a.TYPE, a.wtn, a.REF_WTN
    from aud_sord_trx a, aud_sord_trx b
    where trunc(a.cdate) = trunc(b.cdate)
    and a.wtn = b.wtn
    and a.ref_wtn = b.ref_wtn
    and a.TYPE in ('T','F')
    and a.type = b.TYPE
    order by a.ID
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    May 2002
    Posts
    62
    Here is the data from the table:

    ID cdate T WTN REF_WTN
    ----------- --------- -- ---------- -------------
    18592 09/03/004 F 2173569352 2173569352
    18593 09/03/004 F 2178721197 8152820713
    18598 09/03/004 F 2199809024 2199809024
    18604 09/03/004 F 3124210262 3124210262
    18605 09/03/004 F 3128501393 3128501345
    18606 09/03/004 T 3128501395 3128501393
    18609 09/03/004 F 3128501395 3128501393

    18610 09/03/004 F 3174624761 3178908054
    18614 09/03/004 F 3178231352 3178236324
    18615 09/03/004 F 3178236324 3178236324
    18616 09/03/004 F 3175719549 3177041017
    18618 09/03/004 F 3178759507 3178397514
    18620 09/03/004 F 3178994582 3175462948
    18621 09/03/004 F 3178994793 3178944711
    18622 09/03/004 F 3179242621 3179251181

    Example of the data I wanted is in red color.

    Thanx..
    -Bheem

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select a.ID, to_char(a.cdate,'mm/dd/yyy'), a.TYPE, a.wtn, a.REF_WTN
    from aud_sord_trx a
    order by a.ID

    will return everything, whats special about the two records that you highlighted in red.

    If you want to only show the lowest id numberfor all groupings where cdate,type,wtn, and ref_stn are the same then

    select min(a.ID), to_char(a.cdate,'mm/dd/yyy'), a.TYPE, a.wtn, a.REF_WTN
    from aud_sord_trx a
    group by to_char(a.cdate,'mm/dd/yyy'), a.TYPE, a.wtn, a.REF_WTN
    order by a.ID
    Last edited by beilstwh; 09-21-04 at 17:45.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You can also try..

    Code:
    
    select a.*
      from aud_sord_trx a
     where exists ( select null
                           from aud_sord_trx 
                         where wtn = a.wtn
                            and ref_wtn = a.ref_wtn
                          group by wtn, ref_wtn, cdate
                         having count(wtn)+count(ref_wtn)+count(cdate) >= 6
                       )
      order by 1
    
    OR..

    Code:
    
    select id, cdate, type, wtn, ref_wtn
      from (
    select id, cdate, type, wtn, ref_wtn,
             count(*) over(partition by cdate, wtn order by ref_wtn) cnt
      from aud_sord_trx
             )
    where cnt = 2
     order by 1
    

  6. #6
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    try to add to your where part in original query another condition:

    and a.rowid != b.rowid so your query will be:

    select a.ID, to_char(a.cdate,'mm/dd/yyy'), a.TYPE, a.wtn, a.REF_WTN
    from aud_sord_trx a, aud_sord_trx b
    where trunc(a.cdate) = trunc(b.cdate)
    and a.wtn = b.wtn
    and a.ref_wtn = b.ref_wtn
    and a.TYPE in ('T','F')
    and b.TYPE in ('T','F')
    and a.rowid != b.rowid
    order by a.ID

  7. #7
    Join Date
    May 2002
    Posts
    62
    Thank you so much to madafaka and JMartinez for responding to my post with some good queries.

    Both the following queries solve 70% of the problem. The output is still exactly NOT what I wanted.

    select a.ID, to_char(a.cdate,'mm/dd/yyy'), a.TYPE, a.wtn, a.REF_WTN
    from aud_sord_trx a, aud_sord_trx b
    where trunc(a.cdate) = trunc(b.cdate)
    and a.wtn = b.wtn
    and a.ref_wtn = b.ref_wtn
    and a.TYPE in ('T','F')
    and b.TYPE in ('T','F')
    and a.rowid != b.rowid
    order by a.ID

    The output is:
    ID to_char(cdate) TY WTN REF_WTN
    ----------- ------------------- -- ---------- -------------
    18642 09/03/2004 F 6303229365 6303229363
    18643 09/03/2004 F 6303229365 6303229363

    2nd query:
    select id, trunc(cdate), type, wtn, ref_wtn
    from (
    select id, cdate, type, wtn, ref_wtn,
    count(*) over(partition by trunc(cdate), wtn, ref_wtn) cnt
    from aud_sord_trx
    )
    where cnt = 2
    order by 1

    The output is:
    ID to_char(cdate) TY WTN REF_WTN
    ----------- ------------------- -- ---------- -------------
    18642 09/03/2004 F 6303229365 6303229363
    18643 09/03/2004 F 6303229365 6303229363

    But I want the output with the rows looking like the following:

    18606 09/03/004 T 3128501395 3128501393
    18609 09/03/004 F 3128501395 3128501393



    The output should be with the row where type = T followed by the row with type = F. Everything else should stay the same.

    Thanx for any help.
    -Bheemsen

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The output should be with the row where type = T followed by the row with type = F. Everything else should stay the same.
    Within any RDBMS the "order" of rows is indeterminate, so "followed by" is any oxymoron.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I ran your query with the sample data you provided (If I did respond, is because I had to make *sure* it would work), and I got the records you pointed out. However, to satisfy your requirement, you can try something like:

    Code:
    
    select id, cdate, type, wtn, ref_wtn
      from (
    select id, cdate, type, wtn, ref_wtn,
           count(*) over(partition by cdate,wtn,ref_wtn) cnt,
           min(type) over(partition by cdate,wtn,ref_wtn) f_type,
           max(type) over(partition by cdate,wtn,ref_wtn) s_type
      from t1
           )
     where cnt = 2
       and f_type = 'F'
       and s_type = 'T'
     order by id
    

  10. #10
    Join Date
    May 2002
    Posts
    62
    Thanx JMartinez. That worked like a charm !

Posting Permissions

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