[thelist] Specify WHERE in sub query
Casey Crookston
caseyc at IntelliSoftmn.com
Thu Jun 22 12:37:54 CDT 2006
Oh yea, this is MS SQL
From: Casey Crookston
Sent: Thursday, June 22, 2006 12:35 PM
To: 'thelist at lists.evolt.org'
Subject: Specify WHERE in sub query
Okay,
If you don't feel like reading this very long post, here's the crux of
my question: In a sub query, how do you set a WHERE value to a value in
the current row being selected in the main query? Example:
SELECT stuff1, stuff 2, (select stuff3 WHERE stuff1 = [value of stuff1
for this row]) FROM table.
In real life, I've got a query which looks like this:
~~~~~~~~~~~~~~~~~~~~
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,
sum(oi.quantity) AS NumberSold,
(SELECT sum(oi.quantity) FROM orders o, orderitems oi,
products prod, vendors ven
WHERE o.id = oi.cartid AND prod.id = oi.productid AND ven.id
= prod.vendorid AND o.cleared = 0 AND ven.id = 304) AS InReserve,
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
~~~~~~~~~~~~~~~~~~
I was going to simplify it for reading purposes, but leaving it as is
will make more sense. Right now, you will notice that in the sub query,
the last WHERE item is set to ven.id = 304. The 304 is hard coded, but
it needs to be set to the value of whatever VendorID we are currently
selecting in the big query. Right now the results look like this (note
the IR column):
VenID Vendor ProdID ProdName EnterDate
IA NS IR QOH
304 Albany Golf Club 1289 $10 Off 2006-06-22
105 5 5 100
316 Charlie's Cafe 1287 15$ Gift Cert
2006-04-11 50 2 5 48
280 TGI Fridays 1288 $5.00 Off
2006-05-19 50 3 5 47
They NEED to look like this:
VenID Vendor ProdID ProdName EnterDate
IA NS IR QOH
304 Albany Golf Club 1289 $10 Off 2006-06-22
105 5 5 100
316 Charlie's Cafe 1287 15$ Gift Cert
2006-04-11 50 2 0 48
280 TGI Fridays 1288 $5.00 Off
2006-05-19 50 3 0 47
Thanks!!!
Casey
More information about the thelist
mailing list