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

mamin · #1 (**permalink**) 03-07-05, 16:28

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 ?

r937 · #2 (**permalink**) 03-07-05, 16:52

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

mamin · #3 (**permalink**) 03-07-05, 16:54

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 ?...

r937 · #4 (**permalink**) 03-07-05, 17:08

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'

mamin · #5 (**permalink**) 03-07-05, 17:21

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

r937 · #6 (**permalink**) 03-07-05, 17:37

if the cart contains multiple products from the same category, you may need to add the word DISTINCT after the word SELECT

Internet Services

Web Development

Digital Marketing

Consumer Tech