April 7th, 2005, 06:38 AM
|
 |
www.diebythesword.net
|
|
Join Date: Dec 2003
Location: Leicester, UK
Posts: 202
Time spent in forums: 6 h 49 m 53 sec
Reputation Power: 6
|
|
|
SQL Query problem
Code:
"SELECT DISTINCT tblstock.name AS consumable_Name, Count(tblstock.name) AS Total, Max(tblconsumables.required) AS Required FROM tblconsumables INNER JOIN tblstock ON tblconsumables.id = tblstock.id2 WHERE (tblstock.available)='Yes' GROUP BY tblstock.name"
Ok i have the above code but the problem is it will only show me the consumables that have at least 1 item in stock so if theres none it doesnt say i need to order anymore it just doesnt show it.
My tables are as follows
ConsumableTypes
id
name
required
ConsumableStock
id
name
price
id2 (corrosponds to id of ConsumableTypes)
available
Id like the query to count check the amount of each of the consumableTypes and check it against how many is required and display the results.
Any ideas on a query to do this. I will have a go and see what i can come up with too
TIA
.matt
__________________
Has this been of help? If so click me scales icon at the top right of this post you know you want to.
|
ASP Free Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
