Results 1 to 7 of 7

Thread: query help

  1. #1
    Join Date
    Aug 2006
    Posts
    6

    Unanswered: query help

    id------user_id-----service_id
    1-------11----------490
    2-------11----------492
    3-------11----------500
    4-------12----------492
    5-------12----------493
    6-------12----------494
    7-------12----------495
    8-------13----------492
    9-------13----------500

    Hi, in above table I want to retrieve users who has both service id 492 and 500 so users 11 and 13. How can write sql query of this.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    INTERSECT might be one way to do that:
    Code:
    select user_id
      from your_table
      where service_id = 492
    intersect
    select user_id
      from your_table
      where service_id = 500;

  3. #3
    Join Date
    Aug 2006
    Posts
    6
    Ok, intersect is ok. But how i can do this only with select where statements.

  4. #4
    Join Date
    Aug 2006
    Posts
    6
    select mytable.user_id from
    (select user_id,service_id from mytable where service_id = 492 ) myservice,mytable
    where myservice.user_id = mytable.user_id
    and mytable.service_id = 500

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select a.user_id
    from your_table a, your_table b
    where a.user_id = b.user_id
    and a.service_id = 492
    and b.service_id = 500;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jun 2009
    Posts
    11

    hi bisanthe

    Quote Originally Posted by bisanthe
    id------user_id-----service_id
    1-------11----------490
    2-------11----------492
    3-------11----------500
    4-------12----------492
    5-------12----------493
    6-------12----------494
    7-------12----------495
    8-------13----------492
    9-------13----------500

    Hi, in above table I want to retrieve users who has both service id 492 and 500 so users 11 and 13. How can write sql query of this.

    this is simply we can done by
    select user_id from your_table where service_id in(492,500);

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Simply, we can NOT do that as you've said. Result of your query are USER_ID's marked with the asterisk (*) sign:
    Code:
       USER_ID SERVICE_ID
    ---------- ----------
            11        490
            11        492   *
            11        500   *
            12        492   *
            12        493
            12        494
            12        495
            13        492   *
            13        500   *
    which is far from the required result:
    Quote Originally Posted by OP
    I want to retrieve users who has both service id 492 and 500 so users 11 and 13

Posting Permissions

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