By using this website, you agree to our Terms of Use (click here)
I have a custom generic Inquiry created with multiple joins. I am trying to print all InventoryID's from inventoryItem table with multiple conditions. But the problem is only the Inventory ID's which are meeting the conditions are displayed. How do I get the Inventory Id's with other fields blank even though the conditions are not met?
You need to do this on your joins and not on your conditions. Say I am left joining two tables: Inventory and OtherTable. The OtherTable only contains an inventory ID and field Letter which is either 'A' or 'B'. Your Join should be:
InventoryItem LEFT JOIN OtherTable
Your Join Conditions should be:
InventoryItem.InventoryID EQUALS OtherTable.InventoryID
='A' EQUALS OtherTable.Letter
What this will do is output EVERY Inventory Item row and will attach the OtherTable row(s) that meets the criteria above (Letter = A & ID matches). For those Rows who FAIL to match the criteria, a NULL record will be inserted.
This is the output you're looking for. What the conditions page does is further refine this set. If the conditions page said OtherTable.Letter EQUALS ='A' then we would ONLY see the rows where this was true and not the null rows.
In short, move your conditions into your join and ensure you're NOT using inner joins (those don't create null records). Please go to the link below and look at the graphic, it explains what data is retained in a join.
https://www.w3schools.com/sql/sql_join.asp
Hi Michael. Thank you for your response.
But i did use Left join and in the condition mentioned another one with ISNULL which gave the result with null values.
Right on @michael-hansen! 👍
Glad you got it working! The join will produce null fields. The conditions tab will take the table that is created from your join and only show what is requested in the conditions. So you can always choose to add "or isnull" to the condition OR you can just add the condition to the join and not place it in the conditions tab.
Online Members
