I have the following MySQL query:
SELECT inv.inventory_id, inv.item_id, item.description, inv.quantity, item.class_id, class.description AS class,
class.is_spool, inv.location_id, location.description AS location, location.division_id, division.name AS division,
inv.service_date, inv.reel_number, inv.original_length, inv.current_length, inv.outside_sequential,
inv.inside_sequential, inv.next_sequential, inv.notes, inv.last_modified, inv.modified_by
FROM reel_inventory AS inv
INNER JOIN reel_items AS item ON inv.item_id = item.item_id
INNER JOIN reel_locations AS location ON inv.location_id = location.location_id
INNER JOIN locations AS division ON location.division_id = division.location_id
RIGHT JOIN reel_classes AS class on item.class_id = class.class_id;
The query works exactly as expected as is. What I was trying to do was add a WHERE clause to this query with one qualifier. For example:
RIGHT JOIN reel_classes AS class ON item.class_id = class.class_id
WHERE inv.current_length > 0;
When I do this, all of the results from the RIGHT JOIN are not included in the result. I've not had a ton of experience with advanced queries, but could someone explain why the RIGHT JOIN is excluded from the result set when a WHERE is used, and how to property write the query to include the RIGHT JOIN information?
Thanks in advance.
What you want is:
RIGHT JOIN reel_classes AS class
ON item.class_id = class.class_id AND
inv.current_length > 0;
Your question is why the RIGHT JOIN turns into an INNER JOIN with the WHERE clause.
The reason is simple. For the non-matching rows, inv.current_length is NULL and this fails the comparison.
I would also suggest that you use LEFT JOIN, starting with the table where you want to keep all the rows. Most people find it much easier to understand logic that is "keep all rows in the first table" rather than "keep all rows in some table whose name will come up".