DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: SQL Statement

  1. #1
    Join Date
    Feb 2001
    Location
    Master Control
    Posts
    86
    I am joining a customer table to a phone number table based on customer_id. a customer can have many numbers, but i want only the primary one. however if there is not a primary one, i just want one of any of the others. this is what i have so far.


    SELECT phon.number
    FROM phones phon, customers cust
    WHERE phon.customer_id (+)= cust.customer_id
    AND phon.primary = 'Y';

    any suggestions?

  2. #2
    Join Date
    Jan 2001
    Posts
    153
    select
    case when col2 = 'Y' then col2
    .....
    ......
    end xx
    from
    (SELECT phon.number col1,phon.primary col2
    FROM phones phon, customers cust
    WHERE phon.customer_id (+)= cust.customer_id
    )


    Vijay.s

  3. #3
    Join Date
    Feb 2001
    Location
    Master Control
    Posts
    86
    does this only work with 8i? unfortunately i am working with 7i and CASE is not supported.

  4. #4
    Join Date
    May 2000
    Posts
    58
    select *
    from customers x, phones y
    where x.customer_id = y.customer_id and primary = 'Y' union
    ( select *
    from customer x, phones y
    where x.customer_id = y.customer_id and primary != 'Y'
    and x.customer_id not in ( select x.customer_id
    from customers x, phones y
    where x.customer_id = y.customer_id and primary = 'Y')
    and rownum = 1
    )

  5. #5
    Join Date
    Jul 2001
    Posts
    334
    The simple and powerfull solution is DECODE .
    Use DECODE

    SELECT DECODE (phone.primary,'Y',phon.number,phone.number) ph_number
    FROM phones phon, customers cust
    WHERE phon.customer_id (+)= cust.customer_id;




  6. #6
    Join Date
    May 2000
    Posts
    58
    I thought about DECODE. but it wont work for this situation.
    "however if there is not a primary one, i just want one of any of the others ".
    Outer join will bring up all the rows, What we need is to randomly pick a row ( for a customer ) for those customers
    that dont have a primary phone number.

    Am i assuming anything wrong ? ...

  7. #7
    Join Date
    Jan 2001
    Posts
    153
    can u post me how the data is stored..i am confused abt that..

    Vijay.s

  8. #8
    Join Date
    Feb 2001
    Location
    Master Control
    Posts
    86
    Sorry that the spacing is off...let me know if it makes it confusing.

    Phones

    customer_id phone_number primary
    -------------- ----------------- ---------
    123 555-5555 Y
    123 333-3333
    123 444-4444
    456 333-3030 Y
    888 454-4403
    888 234-4432


    Customers

    customer_id name
    -------------- ------
    123 bob
    456 jon
    888 sue

    So i would want 555-5555 for bob, 333-3030 for jon, and either one of the numbers for sue (but just one of them).

  9. #9
    Join Date
    Jan 2001
    Posts
    153
    select
    col1,
    DECODE((decode(col2,'Y',col3,NULL)),NULL,
    (SELECT PHONE_NUMBER FROM PHONES
    WHERE CUSTOMER_ID = A.COL1 AND ROWNUM<=1)
    ,COL3) PHNO
    from
    (
    SELECT phon.customer_id col1,phon.number col2,phone_number col3
    FROM phones phon, customers cust
    WHERE phon.customer_id (+)= cust.customer_id
    ) A
    Vijay.s

  10. #10
    Join Date
    Feb 2001
    Location
    Master Control
    Posts
    86
    i think something in your syntax is wrong. does that run for you?

Posting Permissions

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


Click Here to Expand Forum to Full Width