Thread: query help

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Hawaii
    Posts
    2
    Rep Power
    0

    Smile query help


    Hello,

    I'm not sure if this can be done using a sql, but I'm trying to select the userids of the rows that doesn't have a certain value, in this example, I want to select the userid who didn't order any pencils, so if they had ordered something else and some pencils, I do not want these rows.
    so in this example, I would like to select only userid 2 (and only one row is desired to be returned); is this possible with sql?
    thank you in advance!

    userid item price
    -------- ----------- ------
    1 pen .25
    1 pencil .15
    1 eraser .50
    2 pen .25
    2 eraser .50
    3 pen .25
    3 pencil .15
    3 eraser .50
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2002
    Posts
    693
    Rep Power
    26
    PHP Code:
    select userid
     from 
    <table>
    where userid not in (select userid from <tablewhere item='Pencil'
  4. #3
  5. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    305
    Rep Power
    11
    you can also write the following query:

    SELECT DISTINCT userid from table1
    WHERE item_name NOT IN ('pencil','eraser','pen')
    /
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Hawaii
    Posts
    2
    Rep Power
    0
    thanks very much guys.
    I like hedge's solution better simply because I don't know what other kinds of items a customer could potentially buy. I did however added in the "distinct" clause.
    thanks again guys!!!!
    this forum is wonderful!

    - Sammy

IMN logo majestic logo threadwatch logo seochat tools logo