Getting child records count ... Like ### responses used here

[josel]

Howdy friends!

I have two tables. One table holds the projects definitions and work requests. The second table, the child table, holds all responses and exchange in relation to first table.

Now, showing the content from a first table is easy. I even learned how to show columns from a second table using INNER JOIN (thanks webmigit & lawson!).

Can some one tell me how can I find the total responses found in second table, the child table?

Thank you all in advance.

Regards;

Jose Lerebours KNOWLEDGE: Something you can give away enlessly and gain more of it in the process! - Jose Lerebours

 

[webmigit]

The easiest way, if you can at this point is to add another column to your first table that holds a number.. and just display the number then there's no fancy joining to be done, just a number to display.. Every time an addition is made, add 1, a subtraction made, minus one.

If you cannot do that, I can help you come up with a solution.

Oh and you're welcome on the last question, I'm sure pcorreia feels the same way and thanks for clicking that little link.. In this forum where everybody helps everybody, that little link is a good way to say thanks. (However if you feel you'd like to send money, my paypal address is... hehe just kidding, I like helping.).

Tony Hicks

 

[webmigit]

Well before I go to bed... you might try something like..

for your query :

SELECT *,RelatedTable.RelID as CountTool
FROM Projects,RelatedTable
WHERE RelatedTable.RelID = Proj.ProjID

And then immediately below that...

<CFSET CountTList=ValueList(WHATEVER_YOU_NAMED_THE_QUERY.CountTool)>

And so in your results table you might have:

Title, Author, Related Items
#ProjTitle#, #theAuthor#, #ListValueCount(CountTList,ProjID)#

If this works, great, if it doesn't, I'll work with you tomorrow when I'm not asleep

Goodnight friends,
Tony Hicks