close ad
 
Important WebAssist Announcement
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

How to return a recordset with multiple JOINS where a row may not exist in a table

Thread began 2/24/2023 6:51 am by Jared Lui | Last modified 2/28/2023 12:11 pm by Ray Borduin | 209 views | 1 replies |

Jared Lui

How to return a recordset with multiple JOINS where a row may not exist in a table

All users have 1 or more lots. So when a user logs in, they will see the amount due for each of their lots. A user can have multiple lots so i have a lots table linked to the user via a owners table as each lot can also have multiple owners. (i have these results in a repeat region). This works as expected for every user.

Each lot can have additional fees associated with it. (late fees, non compliance penalties, etc) These can be multiple so i have them in their own table. (also, within that repeat region)

When logged is as an owner with fees associated, the page works as expected as I am able to echo the values from the DB and repeat this for each lot.

However, when a user logs in that has NO FEES, the code breaks where we try to echo the recordset with fees.

EX.

echo($rsTotal->getColumnVal("arrears"));
echo($rsTotal->getColumnVal("fees"));



Both return correctly if there is a row in the fees table. However, if no row for that user in the fees table, the rest of the code breaks after the arrears echo.

I don't know how to write this so that if there is no fees row, that we simply ignore that part. I tried using an if/else statement but i am not formatting it correctly I cannot get results without errors.

I need your advice. I am sure this could be handled better/differently?

Once I solve this, I need to be able to add all of this together into one total balance. Somehow need to SUM all the lots dues + all arrears + all fees including any that are repeating due to having multiple lots.

Advice on that as well is appreciated!

Jared

Here's the queries:

I have this query which returns the data of the lot of the user that logs in. It works as expected. For context, it returns the amount of the dues owed for that lot each year.

$rsLots = new WA_MySQLi_RS("rsLots",$gm,0);
$rsLots->setQuery(" SELECT * FROM lots JOIN lotOwners ON lots.lotNumber = lotOwners.lotNumber LEFT JOIN users ON lotOwners.ownerID = users.UserID WHERE userID = ?");
$rsLots->bindParam("i", "".($_SESSION['UserID']) ."", "-1"); //colname
$rsLots->execute();



I have this second query that displays any fees this user may have. It works as expected. (but only if the user has a row in the 'fees' table).

$rsFees = new WA_MySQLi_RS("rsFees",$gm,0);
$rsFees->setQuery(" SELECT amtDue FROM fees JOIN lotOwners ON fees.lotNumber = lotOwners.lotNumber JOIN users ON lotOwners.ownerID = users.UserID WHERE userID = ?");
$rsFees->bindParam("i", "".($_SESSION['UserID']) ."", "-1"); //colname
$rsFees->execute();



I've tried to combine these into 1 recordset as follows but have the same issue (which I can understand in this case).

$rsTotal = new WA_MySQLi_RS("rsTotal",$gm,0);
$rsTotal->setQuery("SELECT lots.arrears AS arrears, lotOwners.lotNumber AS lotNumber, fees.amtDue AS fees
FROM lots
JOIN lotOwners ON lotOwners.lotNumber = lots.lotNumber
JOIN fees ON fees.lotNumber = lotOwners.lotNumber
JOIN users ON lotOwners.ownerID = users.UserID
WHERE userID = ?");
$rsTotal->bindParam("i", "".($_SESSION['UserID']) ."", "-1"); //colname
$rsTotal->execute();
Sign in to reply to this post

Ray BorduinWebAssist

It is probably just a matter of getting your table order and join types set up correctly. For instance I think this one might be:

$rsLots->setQuery(" SELECT * FROM users LEFT OUTER JOIN lotOwners ON lotOwners.ownerID = users.UserID LEFT OUTER JOIN lots ON lots.lotNumber = lotOwners.lotNumber WHERE userID = ?");

The left outer join will return all of the rows from the table on the left even if there are no matching rows in the table to the right. I think that is the key to your problem if I'm understanding it right.

Sign in to reply to this post
Did this help? Tips are appreciated...

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...