Inventory Tracker

[timhockey]

Hi

First thanks for your help
Here's my problem

I want to ouput a title (model number) with a table under wich will contain
the dealers who have that model in stock.


In my database i have a table that contain the models and a table that
contains the dealers with the model in stock
I'm not sure how to make the output

Any help would be appreciated!

Thanks!

Tim



<cfquery datasource="database" name="getmodel">
SELECT model from testmodel
ORDER BY model asc
</cfquery>

<cfquery datasource="digiouiz-data" name="getdealer">
SELECT * from inventory WHERE model='#getmodel.model#'
ORDER BY dealerid asc
</cfquery>

[mxstu]

Can you post your output code? Also, you may be able to combine the two queries into one.

[timhockey]

Here's the code

Thanks!




<cfquery datasource="database" name="getmodel">
SELECT model from testmodel
ORDER BY model asc
</cfquery>

<cfquery datasource="database" name="getdealer">
SELECT * from lamtracinventory WHERE model='#getmodel.model#'
ORDER BY dealerid asc
</cfquery>


<cfoutput query="getdealer" group="model">#model#
serie(#getdealer.recordcount# available)</cfoutput>


<cfoutput query="getdealer">
#dealerid </cfoutput>

[mxstu]

Does [lamtracinventory] contain one row per model/dealer or does it contain multiple rows per model/dealer?

[timhockey]

each row contain the model number and the dealer that have it in stock
so let's say 1 dealer have 4 models 1200
it will be 4 rows

[mxstu]

Because you want to display the total number in stock before you display the
dealer details, you will probably need to nest a query within the output loop.
Otherwise, you could just calculate the total as you looped and display the
number at the end of each model section.





<!--- gets all models, even if nothing is in stock --->
<cfquery datasource="myYourDSN" name="getdealer">
SELECT m.Model, i.DealerID, COUNT(DealerID) As NumberInStock
FROM TestModel m LEFT JOIN lamtracinventory i ON m.Model = i.Model
GROUP BY m.Model, i.DealerID
ORDER BY m.Model, i.DealerID
</cfquery>

<cfoutput query="getDealer" group="model">
<!--- use query of query to get total for current model -->
<cfquery dbType="query" name="getAvailable">
SELECT SUM(NumberInStock) AS NumberAvailable
FROM getDealer
WHERE Model = '#getDealer.Model#'
</cfquery>
<b>#model# series (#getAvailable.NumberAvailable#)</b> <br>
<cfoutput>
#dealerid# Amount(#NumberInStock#) <br>
</cfoutput>
</cfoutput>