Results 1 to 8 of 8
  1. #1
    Join Date
    May 2008
    Posts
    1

    Question Unanswered: How can I use AND in Select query on the same field of same table?

    I have one table "order_items", which contains user_id, product_id and some more fields. I want to found the users who have purchased three particular products having product_id e.g 101, 102, 103 (All Three Products not any of Three products)

    Please help me...
    How can I?


    Jitendra

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use aliases

    Id expect a query with 3 left joins would do the job, although there may well be a better solution...

    Code:
    select OI.User_ID, OI.Product_ID,........ from Order_Items as OI
    left join order_items as A on A.User_ID=oi.User_ID
    left join order_items as B on B.User_ID=oi.User_ID
    left join order_items as C on C.User_ID=oi.User_ID
    where A.Product_ID=101
    and B.Product_ID=102
    and C.Product_ID=103
    and a.user_id != NULL
    and b.user_id != NULL
    and c.user_id != NULL
    order by OI.User_ID
    now whether it works or not I haven't got a clue.....

    the left join should extract all records from OI, and those that match in the alisases A,B & C
    where the product code is the required product code in A,B & C
    AND where the use_ID in A, B & C is not NULL (ie there is something in the User_ID for each of the aliases, which we know if its present will be the same as the original table OI because of the join definition

    it should work.. but it'll probably get slated by the knowledgeable ones here

    you may need to check the not equals sign in MySQL,
    likewise you may need to check if there is a better way of expressing comparisions to NULL using a function
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT user_id
      FROM order_items
     WHERE product_id IN ( 101,102,103 )
    GROUP
        BY user_id
    HAVING COUNT(*) = 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Rudy, your example hardly compares to healdem's NZDF classic.

    Healdem, the != sign works fine in MySQL but only with non NULL values, you'd be best to use "is not NULL" here.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Rudy's is NZDF too!

    ...that is assuming one customer can order the same item on more than one order_items instance
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Rudy's is NZDF too!

    ...that is assuming one customer can order the same item on more than one order_items instance
    no it doesn't

    all it assumes is that a customer has purchased those three items

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    But customer 456 has bought product 101 on two separate occasions and 102 once - they will be included on the output.
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, yes, of course :blush:

    okay, HAVING COUNT(DISTINCT product_id) = 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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