SQL Problem

[Andy Trezise]

As is sometimes the case, by the time you've asked a question you already
know the answer............

I wanted to ask:

Other than basically fetching simple data from tables and the odd JOIN
here and there I'm a bit of a novice with SQL.

I'd like to (in one statement) draw information from two tables.

I have a stock (inventory) database and would like to return records
from it along with a sum of a field from another table where the code
matches.

.....i.e.

I wish to return all the information from table 'stock' along with a
further field that is the SUM of a field from an 'orders' table linked by
the stock code.

=SQLEXEC(nSQL,"SELECT * FROM stock WHERE code = 'XYZ'","MY_STOCK") &&
gets me the records from my stock table
=SQLEXEC(nSQL,"SELECT code,SUM(quantity) FROM orders WHERE code =
'XYZ'","MY_ORDERS") && gets information from the orders table

I could then create an index and set a relationship between the two
tables.....

but how do I combine the two statements to return the results in to one
table?

I played around and got the following to work:

SQLEXEC(nSQL,"SELECT t1.*,SUM(t2.quantity) FROM products AS t1 LEFT JOIN
orders AS t2 ON t1.item_code=t2.item_code WHERE t1.item_code = 'XYZ' GROUP
BY t2.item_code","STOCK")

However, this isn't how I'd originally thought it would be done....I thought
it would be more like:

SELECT *,(SELECT SUM(quantity) FROM orders WHERE item_code = 'XYZ') FROM
products WHERE item_code = 'XYZ'

is this possible?????

[richlp]

Does

SELECT stock.*,ord.qty ;
FROM stock ;
LEFT JOIN (select f1,SUM(qty) as qty FROM order GROUP BY f1) ord ;
ON stock.item_code = ord.item_code

work for you????

[richlp]

> work for you????- Hide quoted text -
>
> - Show quoted text -

OOPS......had test fields there

SELECT stock.*,ord.qty ;
FROM stock ;
LEFT JOIN (select item_code,SUM(quantity) as qty FROM order GROUP
BY item_code) ord ;
ON stock.item_code = ord.item_code

If you want specific item_codes you can add a WHERE clause