[thelist] Re-Using A Sub Query Value

Casey Crookston caseyc at IntelliSoftmn.com
Thu Jun 22 14:34:12 CDT 2006



Peter mentioned: I still don't understand why you're using a subquery
here.  

I responded: First, I *know* I am asking a ton of anyone who reads all
this, and I thank you from the heart of my bottom... or whatever.

The silly little example I gave is not at all a good example.  That's
why I posted the entire query into the first post.  I'll try and be
brief to explain what I'm doing...

We're creating a view which will be used as a reporting tool.  The
client is a radio station that has a market place where customers can
purchase discounted gift certificates to vendors around town, and in
turn the vendor gets advertising credit.  Example: Mr. Ed owns a store
that sells widgets for $10.00.  Joe Blow can come to www.mysite.com and
pay $6.00 for a $10.00 widget gift certificate.  The radio station,
which owns and operates www.mysite.com, pockets the $6.00 and gives Mr.
Ed $10.00 of advertising credit towards radio advertising.  Mr. Ed can
specify how many gift certificates he wants to be available.  

Now, when Joe Blow comes to the web site, he can either pay for the gift
certificate with a credit card and print it at home, or he can opt to
come into the radio station and pay with cash or check and pick up the
certificate.  (You'd be surprised how many people do this.)

I'm creating a view which will be used to generate a report of all gift
certificates.

The columns are as follows (listed vertically 'cause email screws it
up.)

~~~~~~~~~~~~~~~~
VendorID

Vendor (Mr. Ed.)

ProductID 

ProductName ($10.00 off a widget)

ProdEnterDate (The date the product was created)

InitialAmount (The starting amount of certificates Mr. Ed wanted to sell
= QuantityOnHand-(NumberSold+InReserve)

NumberSold (The number of certificates requested AND picked up/paid for
= sum(oi.quantity))

InReserve (The number requested but NOT picked up/paid for = SUB QUERY))

Remaining (prod.quantityonhand)
~~~~~~~~~~~~~~~

The trouble is: InitialAmount = QuantityOnHand-(NumberSold+InReserve)
and InReserve = sub query.  Make sense?


Here is the query, so far, again:

SELECT ven.id AS VendorID, ven.name AS Vendor, prod.id AS ProductID,
prod.name AS ProductName, 
prod.EnterDate, prod.quantityonhand+sum(oi.quantity) AS InitialAmount,
	(SELECT  sum(oic.quantity) FROM orders oc, orderitems oic,
products prodc, vendors venc 
	WHERE oc.id = oic.cartid AND prodc.id = oic.productid AND
venc.id = prodc.vendorid AND oc.cleared = 0 AND venc.id = ven.id) AS
InReserve, 
sum(oi.quantity) AS NumberSold, 
prod.quantityonhand AS Remaining 
FROM vendors ven, products prod, orderitems oi  
WHERE ven.id = prod.vendorid AND prod.id = oi.productid 
GROUP BY ven.id, ven.name, prod.name, prod.QuantityOnHand, prod.id,
prod.EnterDate 
ORDER BY ven.name






More information about the thelist mailing list