[thelist] Specify WHERE in sub query

[Casey Crookston]

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