MySQL Query Problem

[cf101]

:confused;

What is wrong with the following Query? It's in the book, but not working.

Select OrderID, OrderDate,
(SELECT Count (*)
From MerchandiseOrdersItems oi
Where oi.OrderID = o.OrderID) AS ItemCount
FROM MerchandiseOrders o
Where ContactID = #Session.Auth.ContactID#
ORDER By OrderDate DESC

[The ScareCrow]

but not working

Does it give an error, return nothing, what ?

What book ?

Can you use sub selects with mySQL ?

Ken

[cf101]

The book is ColdFusion MX by Ben Forta Nate Weiss
Error Message is:
Syntax error or access violation: You have an error in your SQL syntax near
'SELECT Count (oi.OrderID) From MerchandiseOrdersItems oi Where oi.OrderID ' at
line 2




Originally posted by: The ScareCrow
but not working

Does it give an error, return nothing, what ?

What book ?

Can you use sub selects with mySQL ?

Ken

[paross1]

If you are using an "older" version of MySql (before 4.1.x), then you can not perform subselects in your queries.

Phil

[d_adams]

I'm new at this too, but is there a problem using the alias "o.OrderID" before
it is declared in the next line? What if you used the regular table name
"MerchandiseOrders.OrderID" instead?

I may be completely off base, but it just sets off an alrm to me when you use
something before it is declared. :confused;

[The ScareCrow]

The problem with the query is this code
(SELECT Count (*)
From MerchandiseOrdersItems oi
Where oi.OrderID = o.OrderID) AS ItemCount

As I suspect that the version of mySQL being used does not allow sub selects

Ken

[mxstu]

Assuming you're using a version of mySQL that supports subqueries (mySQL 4.1.?
or higher)...

Just remove the space between Count and (*).

--- Tested with mySQL 4.1.9
SELECT OrderID, OrderDate,
(SELECT Count(*)
FROM MerchandiseOrdersItems oi
WHERE oi.OrderID = o.OrderID) AS ItemCount
FROM MerchandiseOrders o
WHERE ContactID = #Session.Auth.ContactID#
ORDER By OrderDate DESC

[cf101]

Hi guys!
It looks like old versions of MySQL do not support Sub Select quiries.