[SOLVED] Subuery problem

[gabrielp]

Hi

 

I'm trying to do a simple subquery.

 

I have the following tables:

 

Orders = Purchases made

ordercart = list of products that belong to each purchase

Categories = product list

Designers = designer that have created that product

 

ordercart.product = categories.catid

orders.order_number = ordercart.id

categories.designerid = designers.designerid

 

 

I need all the order numbers that have products that belong to a specific designer so I can calculate commissions of sale for a given day. If I run these 2 queries by their own, they work ok, if I run them as a subquery, it gives me the following error.

 

What's wrong with the syntax??

 

SQL-query: Documentation

 

 

SELECT DISTINCT order_number

FROM orders, ordercart

WHERE orders.confirmed =1

AND orders.order_number = ordercart.id

AND DATE_FORMAT( thedate, '%d' ) =03

AND DATE_FORMAT( thedate, '%m' ) =04

AND DATE_FORMAT( thedate, '%Y' ) =2008

AND product

IN (

SELECT catid

FROM categories

WHERE designerid =9

)

LIMIT 0 , 30

 

MySQL said: Documentation

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT catid FROM categories WHERE designerid = 9) LIMIT 0, 30'

[fenway]

Which db version?

[gabrielp]

MySql 5

[fenway]

Hmmm... we'll, I'd quote those string literals.

[gabrielp]

all the values are numbers, no strings here.

 

What is strange is that if I do the following query it works:

 

SELECT DISTINCT order_number

FROM orders, ordercart

WHERE orders.confirmed =1

AND orders.order_number = ordercart.id

AND DATE_FORMAT( thedate, '%d' ) =03

AND DATE_FORMAT( thedate, '%m' ) =04

AND DATE_FORMAT( thedate, '%Y' ) =2008

 

without the subquery....

 

and even the inner part of the subquery works as a single query, so there are no mistakes in column or table names...

 

 

 

 

[fenway]

all the values are numbers, no strings here.

DATE_FORMAT() always returns a string regardless... but the only reason to get an error like that with a subquery (i.e. at the SELECT keyword) is a version issue.

[gabrielp]

I double checked and my Hosting control panels says mySql 5, but my phpMyAdmin control panel says

MySQL 4.0.27-standard running on localhost

 

in this version, the syntax is different?

[aschk]

Perform the following query to be sure what version you are connecting to:

SELECT @@version;

 

Also, I suspect you should drop the DISTINCT as it's bad practise, and instead replace it with the correct JOIN and GROUP clause.

If you explain in plain text english what information you want out, a sample output, a sample input, and your schema i'm sure we can clarify how you can better structure this query and your database layout.

[gabrielp]

SELECT @@version; gives me 4.0.27-standard

 

 

It's really frustrating as I need to continue working and this subquery is not working, and is a simple one...

[fenway]

in this version, the syntax is different?

It's not different as much as it is unsupported. Try this (untested):

 

SELECT DISTINCT order_number
FROM orders 
INNER JOIN ordercart ON orders.order_number = ordercart.id
INNER JOIN categories ON orders.product = categories.catid
WHERE orders.confirmed =1 AND thedate = '2008-04-03' AND categories.designerid =9

I'm just guessing at tables here.

[gabrielp]

Thanks fenway !!

 

That did the trick !

 

Thanks so much for your time.

 

Gabriel.