Subquery to count related records
Posted: Thu Sep 20, 2012 4:57 am
Hi,
I read in the wiki (http://wiki.nubuilder.com/tiki-index.ph ... uilderDocs):
"DO NOT use a SELECT statement inside another SELECT statement here, because nuBuilder can't handle it."
There's a way to bypass this limitation for a query like this?
The only way I found was to replace the SQL statement in the form with:
and put this code in the CustomCode > BeforeBrowse:
This works fine, but I wonder if there was a simpler solution.
Thank in advance.
I read in the wiki (http://wiki.nubuilder.com/tiki-index.ph ... uilderDocs):
"DO NOT use a SELECT statement inside another SELECT statement here, because nuBuilder can't handle it."
There's a way to bypass this limitation for a query like this?
Code: Select all
SELECT *, (SELECT COUNT(*) FROM table2 WHERE table1_id = table2_table1_id) AS num FROM table1Code: Select all
SELECT * FROM #browseTable# Code: Select all
// Create a temporary table with the result of the first query
$query = nuRunQuery('CREATE TABLE #browseTable# SELECT * FROM table1');
// Add a column to temp table just created
nuRunQuery('ALTER TABLE #browseTable# ADD num INTEGER(5) NOT NULL');
// Select all record from temp table again
$rs = nuRunQuery('SELECT * FROM #browseTable#');
// For each record in table1, count how many related record in table2
while ($row = db_fetch_object($rs)) {
$id = $row->table1_id;
$num = db_fetch_row(nuRunQuery("SELECT COUNT(*) FROM table2 WHERE table2_table1_id = '$id' "));
// Insert the number of records in the new column
nuRunQuery("UPDATE #browseTable# SET num = " . $num[0] . " WHERE table1_id = '$id' ");
}
// Select all record again to open the form
nuRunQuery('SELECT * FROM #browseTable# ORDER BY ....');Thank in advance.
