Custom SQL Query

Hi Ray.

I need help with an SQL query if you can.

So in our admin system I have an html table that shows a list of accounts from a database table ( Accounts Table ) . I’ve removed name, email and other columns that aren’t necessary for this.
Each of these accounts has a members list that is stored in another table ( Members table ). These tables have a relational value which is the Account_id primary key in the Accounts table and the Account_ID foreign key in the members table.

The members table has a ‘Fee’ column which can have a value of Paid or Unpaid.

So this is what I want to do…

When ALL values of the Fee column for a related account has a value of paid, I want to highlight the row, in the html table showing the accounts list, in a different colour.
An example using the sample tables would be account_id 3

I’m assuming I need to use COUNT in the query. I’m just not sure how to build this and then reference it to highlight each row In the html table.

I hope this makes sense.

Thanks in advance.

I think something like:

SELECT Accounts.*, Count(Members.Account_ID) AS unpaid FROM Accounts LEFT OUTER JOIN Members ON Members.Account_ID = Accounts.Account_ID AND Fee <> 'paid' GRUOP BY Accounts.Account_ID

Then you can use an IF statement to check if the value of "unpaid" in the recordset is equal to zero or not to change the color of the column.

OK so this is creating a new recordset using COUNT function and GROUP BY statement.

But the problem is the html table showing the account list is using another recordset and a repeat region, so I think I’m right in thinking that binding data from the new recordset wont work.

How can I marry this together.

I’ve attached the page. SO the recordset that populates the list is rsActiveApps. The recordset that I have replicated your suggestion is rsAllPaid.

The table where I need to highlight the rows is under <h1 class="page-head">Admin - Live Applications</h1> line 383

You could either integrate the COUNT and GROUP BY statements into your existing recordsets, or you could add a new recordset inside of the repeat region and get the count for each row separately by adding an additional WHERE clause to only get the data for that row.

Having problems with this Ray..

I've tried numerous manual builds of the query but every time get SQL syntax errors.

So I then used the WA query builder and even that gives a syntax error.

the attached screen shot shows 2 queries ( recordsets ) rsActiveApps which is the original setup for the list. And rsActive which is the new one built from the query builder that doesn't work.

Also attached is page with these on.

Where am i going wrong?

Thanks in advance!

I'll need to debug this in a premier support ticket. I'm not that familiar with your database structure to determine what might be wrong with your sql. I'd try copy and pasting the sql statement into phpMyAdmin and see if it gives you a better error you can use to find the problem.