Matthew,
>CREATE PROCEDURE 'xxxxx'.'CrossSelling' ()
>BEGIN
>SELECT TOP 5 OrderDetails.ProductID, OrderDetails.ProductName,
Count(OrderDetails.ProductID) AS CountOfProductID
>FROM OrderDetails
>WHERE (((OrderDetails.OrderID) In (select OrderID from OrderDetails
where ProductID=[pid])))
>GROUP BY OrderDetails.ProductID, OrderDetails.ProductName
>HAVING (((OrderDetails.ProductID)<>[pid]))
>ORDER BY Count(OrderDetails.ProductID) DESC;
>END
MySQL syntax != MSSQL syntax. No TOP in MySQL---use LIMIT (and it's
slower). Also IN(SELECT...) is abysmally slow. For alternatives see "The
unbearable slowness of IN()" at
http://www.artfulsoftware.com/infotree/queries.php.
PB
-----
Matthew Stuart wrote:
Hi, I have several procedures that I have taken from an old Microsoft
database, and I have tired to use them in a MySQL 5.1.32 database, but
I am getting errors when trying to input them. There are three in
total that I am struggling with and would appreciate some guidance...
This is a cross selling query:
CREATE PROCEDURE 'xxxxx'.'CrossSelling' ()
BEGIN
SELECT TOP 5 OrderDetails.ProductID, OrderDetails.ProductName,
Count(OrderDetails.ProductID) AS CountOfProductID
FROM OrderDetails
WHERE (((OrderDetails.OrderID) In (select OrderID from OrderDetails
where ProductID=[pid])))
GROUP BY OrderDetails.ProductID, OrderDetails.ProductName
HAVING (((OrderDetails.ProductID)<>[pid]))
ORDER BY Count(OrderDetails.ProductID) DESC;
END
Error is: 1064
'5 OrderDetails.ProductID, OrderDetails.ProductName,
Count(OrderDetails.ProductID' at line 3
If somebody could give me an idea of what is wrong here with regards
to it working with MySQL, I might be able to make the other two
problem functions work with out too many tears.
Thanks.
Mat
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.0.238 / Virus Database: 270.12.2/2074 - Release Date: 04/22/09 08:49:00
