Sub-Select query

[Christos Vorkas]

Dear all,

I have the following query. When executed in MySQL I get an error on the
first sub-select, and I can't find why:

select slbc.stockcode, s.description, slp.qtyinstock, smax.pluqty,
(slp.qtyinstock / smax.pluqty)
from stocklevelbc slbc, stock s, stocklevel slp, stock smax,
stocklevelbc slbcmax
where slbc.stockcode = s.stockcode
and slbc.restcode = NIC-PAG-1
and slp.plu = s.plu
and slbc.restcode = slp.restcode
and smax.stockcode=slbcmax.stockcode
and slbcmax.stocklevelid in 
(select z.stocklevelid from stocklevelbc
z, stock ss where z.restcode=slbc.restcode and z.stockcode in (select
sss.stockcode from stock sss 
where sss.plu = s.plu) order by
qtytransfered desc)

The tables in use are:

STOCK
---
StockCode   varchar(30)
Description varchar(100)
PLU varchar(20)
PLUQty  decimal(10,4)

STOCKLEVELBC
--
StocklevelIDint(11) auto_increment
RestCodevarchar(20)
StockCode   varchar(30)
QtyInStock  decimal(10,4)
QtyTransfered   decimal(10,4)

STOCKLEVEL
--
StocklevelIDint(11) auto_increment
RestCodevarchar(20)
PLU varchar(20)
QtyInStock  decimal(10,4)
QtyTransfered   decimal(10,4)

The idea is that there are several stock codes that are the same
product, but different packagings. All same products have the SAME
*PLU* code, so that they are grouped together. When sales are made
(these are for a restaurant), the sale is done from the PLU and NOT from
the stockcode, because products are purchased in bottles, but sold in
glasses, so we cannot know which bottle was opened to sell a glass of
whisky for example.

So I have the QtyInStock in the STOCKLEVEL table showing the stocklevel
for each PLU.

Now, I want to know how many BOTTLES this QtyInStock refers to, by
dividing the QtyInStock by the number of the PLUQty for the MOST
PURCHASED TYPE OF PACKAGING/BOTTLE

For example:

Carlsberg beer (small bottle - 33CL) - Bottles transferred = 500 bottles
Carlsberg beer (large bottle - 63CL) - Bottles transferred = 200 bottles

Carlsberg beer (common PLU) - QtyInStock = 20,000CL

Results should be:
= Most popular packaging - Small Bottle 33CL
= QtyInStock represented as a number of most popular packaging in stock
= 20,000 / 33 = 606.06

Any ideas VERY much appreciated


Regards,
Christos Vorkas



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

BeginTrans - Commit - Rollback on MySQL

[Christos Vorkas]

Hi All,

I am trying to establish a transaction-oriented method of posting
changes on the MySQL database. I'm using MyODBC 3.51 and VB6.

Using the ADO Controls, I use connection.BeginTrans to start the
transaction, followed by a series of ado.updates, and finally a
connection.committrans to close the group.

However, breaking the program before the committrans, and stopping the
application before the committrans is executed (i.e. trying to simulate
a crash in the middle of the transaction), should result in NO updates
done to the MySQL tables, but this is not the case. ALL updates done up
to that moment have been recorded and finalized in the database.

Any ideas why this is not working as it should?

Any comments much appreciated.

Best regards,

Christos Vorkas

-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php