A little help with some code and theory

[NCX001]

Hey guys, I'm kinda new to this whole programming thing so bare with me please. I have a little problem that I just can't seem to get my hear around. I have a working on an inventory management page. It keeps track of what software is installed where and how many copies we have, and serials and such. Well, this page was set up long before I started here and it wasn't too friendly. So I'm editing it and adding some new features. Well one of them is displaying the “available installs count” with the search results.

Here is my problem. The way the system keeps track of what software is installed where is by adding taking the appId from the table it with all the app's information on it (the sn, name, max installs, etc) and creating a new entry in another table that indexs that appId and the workstation it is installed on. (along with an Install id) well the only way to check to see how many copies is installed is to get the record count for a particular appId.

This is where I run into trouble. When a user searches for apps the results page displays the apps that still have installs available. So where the record count of getInstallCount for whatever appId is less than the max installs. Well what is the best way to do this since to get the record count of getInstallCount I need an appID and the appID isn't found until I do the search query. What I'm using now is this but it always returns 0 for current Install Count. See the attached Snippet of code:

I see what it is doing. It is getting the isntallCount for all appIds, but I dont' know how to narrow it down without doing the search first, and I don't know how to do the search with out getting the install count first. Any ideas or advice would rock guys. Like I said I'm kinda new to this programming stuff but it is awesome! Thanks!

Kevin

[paross1]

Not sure if this is what you are looking for, but something like this should give you the apps in workstationApps that are still available (have fewer "installs" than maxConcurrentInstalls).

SELECT w.*
FROM workstationApps w
WHERE (SELECT COUNT(wi.appID)
FROM workstationAppIndex wi
WHERE wi.appID = w.id) < w.maxConcurrentInstalls
<cfif isDefined("form.searchCriteria")>
AND w.searchType like '%#form.searchCriteria#%'
</cfif>

(NOTE: I'm not sure what you meant by #form.searchType# like '%#form.searchCriteria#%' except that you probably meant to say something like searchType like '%#form.searchCriteria#%')

Phil

[NCX001]

hey that worked... could you explain to me what it is you're doing? I am guessing that you are using the w. and wi. to make query, query more than one table.

and the #form.searchType# is from a drop down menu that determines what field the search is running in. Like ID, or Product Name, and the such.

[paross1]

The w and wi are just table aliases that make it easier to referrence those tables by column when joining multiple tables (avoids having to prefix column names with the entire table name, etc.) When doing a self-join (joining a table to itself), the use of table aliases is required, otherwise you would have no other way to tell which column belonged to which "instance" of table.

The query itself is pretty simple. It is just using a correlated subquery to select only those instances of workstationApps where the count of appID instances in workstationAppIndex is less than the amount specified in the maxConcurrentInstalls for the same appID.

Also, I do see what you are doing with #form.searchType#, I was just making sure that it was what you really intended to do.

Phil

[NCX001]

Thanks a lot Phil one more quick question. How would I show the count of current installs. When looping through the results I want to display the number of current installs without having to do another query. Is there a way that I can just pick the count out of the query?

[Dan_Bracuk]

Query of Queries is your freind. Google "coldfusion Query of Queries" for details.