Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    24

    Unanswered: subquery doesn't work in mysql (was "SQL Statement Help needed")

    I am having issue with query which works in MS SQL But does not work in MySQL..Can someone help me how can I do same or similar with MY SQL..

    My MS SQL Statement is as follows

    select * from products where categoryid in (select distinct categoryid from tblcart,products where products.sku = tblcart.productid and cartID='767867867868')


    How can achieve same with mySQL ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the only thing that could possible be wrong is that you aren't on the current release, 4.1, which has been in production status since october, and which supports subqueries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Posts
    24
    Quote Originally Posted by r937
    the only thing that could possible be wrong is that you aren't on the current release, 4.1, which has been in production status since october, and which supports subqueries
    I have ticket open with my hosting company on version as you mentioned. Let say they do not have 4.1 installed then what should I do..I have to write two queries to do this task ?...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    caution: untested --
    Code:
    select p2.* 
      from tblcart 
    inner
      join products as p1
        on tblcart.productid 
         = p1.sku 
    inner
      join products as p2
        on p1.categoryid
         = p2.categoryid
     where cartID = '767867867868'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Posts
    24
    Quote Originally Posted by r937
    caution: untested --
    Code:
    select p2.* 
      from tblcart 
    inner
      join products as p1
        on tblcart.productid 
         = p1.sku 
    inner
      join products as p2
        on p1.categoryid
         = p2.categoryid
     where cartID = '767867867868'
    Thank you for your help...I will try and let you know

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if the cart contains multiple products from the same category, you may need to add the word DISTINCT after the word SELECT
    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
  •