Results 1 to 6 of 6

Thread: Query Help

  1. #1
    Join Date
    Jun 2013
    Posts
    3

    Question Unanswered: Query Help

    I need to write a SQL for below requirment :
    Customer table :
    Customer Plan
    1 A
    1 B
    1 E
    2 A
    2 B
    2 C
    2 D
    2 E
    2 F
    3 A
    3 B
    4 A
    5 B

    The customer which has ONLY plan A or B (in my above example Customer number 3 ,4 and 5 should be returned )

    I belive there might be some easy trick to do it but I cant think of any right now. I would appreciate any help.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by RishiJ View Post
    I need to write a SQL for below requirment :
    . . . E t c . . .
    I belive there might be some easy trick to do it but I cant think of any right now. I would appreciate any help.
    To bad, this is one of the medium to easy complex homework assignments.
    You should try and solve it yourself!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Jun 2013
    Posts
    3

    Smile

    I know it was easy but somehow I was going in wrong direction and got frustated

    I got the answer for this one after 'anacedent' pointed me in right direction:

    SELECT DISTINCT CUSTOMER_ID FROM CUSTOMER
    MINUS
    (SELECT DISTINCT CUSTOMER_ID
    FROM (SELECT CUSOTMER_ID, PLAN FROM CUSTOMER
    MINUS
    SELECT CUSTOMER_ID, PLAN
    FROM CUSTOMER
    WHERE PLAN IN ('A', 'B')))

    Thanks guys for the help!!!

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Quote Originally Posted by RishiJ View Post
    I know it was easy but somehow I was going in wrong direction and got frustated
    . . . E t c . . .
    I got the answer for this one after 'anacedent' pointed me in right direction:
    Great, you are learning.
    Now remember that there are may ways to "skin" a cat (as the saying goes),
    So here is one of many alternative solutions:
    Code:
    SQL> WITH customer
         AS (SELECT 1 customer_id, 'A' plan FROM DUAL UNION
      2    3           SELECT 1, 'B' FROM DUAL UNION
      4           SELECT 1, 'E' FROM DUAL UNION
      5           SELECT 2, 'A' FROM DUAL UNION
      6           SELECT 2, 'B' FROM DUAL UNION
      7           SELECT 2, 'C' FROM DUAL UNION
      8           SELECT 2, 'D' FROM DUAL UNION
      9           SELECT 2, 'E' FROM DUAL UNION
     10           SELECT 2, 'F' FROM DUAL UNION
     11           SELECT 3, 'A' FROM DUAL UNION
     12           SELECT 3, 'B' FROM DUAL UNION
     13           SELECT 4, 'A' FROM DUAL UNION
     14           SELECT 5, 'B' FROM DUAL)
     15    SELECT *
     16      FROM (  SELECT customer_id
     17                   , COUNT (*) cnt
     18                   , SUM (DECODE (plan, 'A', 1, 0)) cnta
     19                   , SUM (DECODE (plan, 'B', 1, 0)) cntb
     20                FROM customer
     21            GROUP BY customer_id)
     22     WHERE cnt = cnta + cntb
     23  ORDER BY 1
     24  /
    
    CUSTOMER_ID        CNT       CNTA       CNTB
    ----------- ---------- ---------- ----------
              3          2          1          1
              4          1          1          0
              5          1          0          1
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Jun 2013
    Posts
    3
    Wow never thought it could be done this way also. Thanks for teaching me new trick.

Posting Permissions

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