Thread: Oracle query

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0

    Oracle query


    ok I am through pulling my hair out and decided to let you guys help me .

    I have 2 tables
    Table 1
    ID - Container - internalid
    100 A01001 1
    101 A01002 3
    102 A01003 2
    103 A01001 4
    104 A01003 5

    Table 2
    ID Container ShortName
    1 A01001 A01
    2 A01002 A01
    3 A01003 A01

    My problem is this
    1 - I start with the internal id of 1
    2 - I need ot find the only container in table 1 with 1 record

    Expected output = 102

    My attempts have been
    select
    id, container
    from
    table 1
    where
    substr(container,1,9) in
    (
    select substr(container,1,9)
    from table 1
    where ID in (1)
    ) and
    having count(container) = 1
    group by id, container -- I really don't want to group here, but have to and this screws up the query
    order by container


    Thanks in advance
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    I'm not sure why you need table2 at all, so maybe I didn't understand your question completely.

    But this returns those rows where there is only one container:
    Code:
    select id, 
           container, 
           internalid
    from (
       select id, 
              container,
              internalid,
              count(*) over (partition by container) as cnt
       from table_1
    ) 
    where cnt = 1
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0
    shammat,

    Thanks for the response. I did not give a good sample dataset for the question.

    Table 1
    ID - Container - internalid
    100 A01001 1
    101 A01002 3
    102 A01003 2
    103 A01001 4
    104 A01003 5
    105 A01004 6
    106 A02001 7
    107 A02001 8
    108 A02002 9
    109 A02003 10

    Now the query that you gave will return
    A01002, A01004 A02002 and A02003. I need to return only one value per substr(container,1,3).
    so we could get either A01002 or A01004. (it doesn't matter which one.)

    Since table 2 has the A01, A02,A03 values, I thought it would be eaiser to use that table aid in the joins.

    If I try to do a filter on substr(container,1,3) I end up with what I need, however once I group on the container, it reverts back to the all the values per container.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    5
    Rep Power
    0
    Hi,

    I created a table (table_1) with the following data as posted in your first question:
    100 A01001 1
    101 A01002 3
    102 A01003 2
    103 A01001 4
    104 A01003 5

    If I have understood your question correctly then you wish to find the container having a unique value in table_1 , i.e no other row has that container value. So as per the data above, that row should be the one where internal id is 3. Because the container value of A01002 is not present in other rows. Please see my query below:

    select id , container,internalid from table_1 where container in (
    select t1.container
    from table_1 t1
    group by t1.container
    having count(t1.container) <= 1);

IMN logo majestic logo threadwatch logo seochat tools logo