What’s the proper way to join two tables in BAQ to return records where something is NOT in the second table? I’m trying to show all runout parts that don’t have inventory - e.g. There’s a Part.PartNum record where PartNum.Runout = TRUE, but no record in the PartBin table (aka, no inventory).
My search-fu came up empty… Thanks for the help!
If I understand your question, click the little square in the middle of the join in the designer, and select “All rows from Part” in the lower right. This makes it a left join. Then add a subquery condition that PartBin.Company is null. (Or any field of PartBin that would never be null.)
You can also set the isnull criteria on the PartBin table and it would mark the table as having criteria. I always forget to look for subquery criteria. 
Hi @askulte ,
Any chance you mean the same functionality as Except in SQL?
Regardless, I would do a left (or right) outer join (depending on the table that you want to keep) and then in the next subquery I would filter where a certain column is null.
1- SubQuery1 : Left outer join on the respective columns (Company, Part, … )
2- SubQuery2: Import SubQuery1 and Filter on it where a certain field from second table is null
The except exists in the BAQ. It is a subquery type. It’s always confused me a bit, but it does work.
The way I always do it is to include all of the parts from Part table, and only the ones from PartBin that match. Then, as @gpayne said I look for IsNull on the PartNum record in the PartBin. That will tell you what parts you have defined that have no stock.
Hi @Banderson ,
Absolutely, EXCEPT and UNION within Epicor haunted me for some time, lol.
But they do work (I find them inconvenient though especially if you need to do a UNION or EXCEPT in the middle of the flow).
Still I don’t think EXCEPT alone would solve @askulte 's issue here.
I think he can join and simply filter where PartNum.Runout=TRUE on Erp.Part table and PartBin.company is NULL on Erp.PartBin since NULL indicates lack of relation.
@askulte ,
Can you try this and let me know if it works?
Part2PartBIN.baq (25.3 KB)
Awesome sauce. Thanks! That worked.
Shizar115 - How is this different than Kevin and Greg’s method, but within 1 query, instead of adding an additional subquery?
It is not different however I find it more readable for one (debatable though) and secondly most of the time for what I do one subquery is not enough and I usually filter in the next phase (in other words I expect more to be done in subquery 2, for instance joining with PartWhse).
Regarding performance I am not sure, what I sent could be slower (would not be surprised in that case).
On a side note (not sure if in future you might need this), where clause filters on the result of join, but if you need to filter a table before join happens (ie filter PartBin first, then join with Part), you would need to move the WHERE clause to ON section.
This is a good example that I found:
https://stackoverflow.com/questions/15077053/filter-table-before-applying-left-join
