Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2002
    Posts
    15

    Unanswered: select from same table (Oracle 8i)

    How to make such a query?

    table test
    id name value
    -------------------------
    1 A B
    1 C D



    I want to get the result, when
    id = 1
    and (name = 'A' and value = B')
    and (name = 'C' and value = 'D')

    id
    ------------------
    1

    I tried to use :
    select id from test t1, test t2
    where t1.id = t2.id
    and (t1.name = 'A' and t1.value = 'B')
    and (t2.name = 'C' and t2.value = 'D')

    It works, but there should be some better solution.
    Any suggestion?

    tienchihwang

  2. #2
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: select from same table (Oracle 8i)

    I think in this case, selfjoin is a best solution. Cheers.

    Originally posted by tcwang66
    How to make such a query?

    table test
    id name value
    -------------------------
    1 A B
    1 C D



    I want to get the result, when
    id = 1
    and (name = 'A' and value = B')
    and (name = 'C' and value = 'D')

    id
    ------------------
    1

    I tried to use :
    select id from test t1, test t2
    where t1.id = t2.id
    and (t1.name = 'A' and t1.value = 'B')
    and (t2.name = 'C' and t2.value = 'D')

    It works, but there should be some better solution.
    Any suggestion?

    tienchihwang

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    What is wrong with ...

    select...

    PHP Code:
    where ID AND 
       (
         (
    name 'A' and value 'B') or 
         (
    name 'C' and value 'D')
       ) 
    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    Bill,

    He wants AND, not OR.

  5. #5
    Join Date
    Feb 2002
    Posts
    15
    hi,
    What I need is 'AND', not 'Or"
    for example:

    id name value
    --------------------
    1 SSN 123456789
    1 Birth_Date 06012003

    I want to find out this person (id = 1), correspoing to
    the two criteria ( ssn = 123456789 and birth_date = 06012003).
    Any suggestion will be great appreciated.

    tienchihwang


    Originally posted by billm
    Hi,

    What is wrong with ...

    select...

    PHP Code:
    where ID AND 
       (
         (
    name 'A' and value 'B') or 
         (
    name 'C' and value 'D')
       ) 
    Hth
    Bill

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Sorry about that - I should pay more attention to the question :-)

    The only way that springs to mind to not use the self join is using the intersect operator...

    PHP Code:
    select id from t1 where name 'SSN' and value '123456789'
    intersect
    select id from t1 where name 
    'BIRTHDATE' and value '06012003' 
    I would imagine the intersect is likely to result in a more stable plan regardless of the data volumes... it would be worth you checking both queries anyway.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  7. #7
    Join Date
    Feb 2002
    Posts
    15
    Thanks Bill,
    It works.
    But if I had several pair values. Is this the only way I can use to?

    select id from t1 where name = 'n1' and value= 'v1'
    intersect
    select id from t1 where name = 'n2' and value= 'v2'
    intersect
    select id from t1 where name = 'n3' and value= 'v3'
    intersect
    ......
    ...

    Tien-Chih Wang

    Originally posted by billm
    Hi,

    Sorry about that - I should pay more attention to the question :-)

    The only way that springs to mind to not use the self join is using the intersect operator...

    PHP Code:
    select id from t1 where name 'SSN' and value '123456789'
    intersect
    select id from t1 where name 
    'BIRTHDATE' and value '06012003' 
    I would imagine the intersect is likely to result in a more stable plan regardless of the data volumes... it would be worth you checking both queries anyway.

    Hth
    Bill
    Last edited by tcwang66; 04-14-04 at 17:02.

  8. #8
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Tien,

    Yes it should still work - but you should check performance using explain plan or preferably tkprof for the self join versus the intersect.

    As I said, I would expect the intersects to produce a more stable plan, but the self join could possibly be more efficient for you at this moment in time.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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