How to Use Conjugate SQL JOIN Queries to Fix Missing Records
During the QA section of a recent release, I was tasked with fixing a bug for missing user records. This release focused on a coupon assignment system where users could be given different types of coupons by our sales team.
While testing, we found that not all users were accessible from the coupon assignment dashboard that we had built. Sorting the coupon balances, it was clear that any user that did not have coupons would never be able to get coupons assigned to them as they would never appear as a row in our table. For this bug I needed to figure out why these records were not showing up in a bulk call to our users endpoint.
Faulty WHERE Statement on JOIN Table
Looking at the controller action we see that we have a basic setup of a call to query records:
Because there was no filtering performed on the records before we pass our class variable to the view, I knew that our issue had to be in the query_records method.
Diving into it I saw the following code:
Here we are joining our Users table with our Product::Coupons table to be able to access the coupons. Because a Coupon belongs to a User, but a User does not know all the coupons they may have, we are starting with the Users table to get all Users and doing a full join on the Coupons. This will include every Coupon and User record whether or not they form an entire record.
After this join, we are filtering to make sure that we only display the coupons that are available to a user. Among other fields, the Coupons have a ‘expiration_date’ and a ‘product_number.’
While the customers may like to have coupons that last forever, we implement an expiration date as a business requirement to ensure we can closely forecast revenue for each quarter. Along with forecasting revenue, the business team wants to know what people are using coupons on. Because of this, we assign the product_number or product_id to both allow for coupon use tracking and mark a coupon as consumed.
However when we run this call we are dropping all user records that have 0 coupons. Why is that?
After inspecting our SQL queries I found that the issue had to do with the way we were calling our WHERE clauses. The current where clause is “WHERE coupons.expiration_date >= CURRENT DATE/TIME()” which returns the record if the expiration date is later than the current date.
What happens when there isn’t a date? When a record in our JOIN table has a user, but no coupon, it will be filtered out because even though the date is not less than the current_date, it does not meet our where criteria of having a value that is less.
To get around this, we need to make our where statement test conjugate conditions. The solution can be seen in the following code snippet:
Here we see that there is a second condition that accounts for user records that do not have a coupon. Now if there is no coupon.expiration_date, we will still return the record as we account for it with our user check!
The same logic can be applied to the product_number and any other attribute that we would want to check against. After pushing this for a review, I found a way to store all this logic in a single where clause:
Concise and to the point!
Conclusion
Joins are one of the more complex aspects of learning SQL. Once you figure out which records are and aren’t included in the basic join statement, the next wrinkle is layering on and understanding filtering clauses. I hope that this article helped you better understand a common query issue. Leave a comment below of any SQL tricks you have encountered.
**The code and example are changed from the actual production code. Everything in this article is an abstract example and applies principles, not actual code for the solution.**
Notes
