I want to get customers that exist in the customer table but not in the orderhed table. How can I get that in a BAQ?
In SQL I can use a where not exists. What does that translate to in a BAQ?
select * from Customer
where NOT EXISTS (select * from OrderHed where OrderHed.CustNum = Customer.CustNum)
Do a left join from Customer to OrderHed on CustNum and only return rows where OrderHed.CustNum is null.
There is EXISTS operation in criteria, and NOT checkbox
You can also do an except query. You would put your customers on the top level, then the orderHead on the except level, and it will give you a list of customer numbers that don’t have orders. Then use that to get what you need on the next level up.
That worked. Thank you Banderson!
@Olga, for grins and giggles I tried the not-exists, and that just returned nothing. I don’t believe that works as expected. Or am I doing something wrong there?
That would be sad.
I have not check recently, but you can looks what query is generated in the server using trace flag for BAQ statement.
You can add a subquery with OrderHed, groupby ordernum and custnum, sum(1) to get the number of orders.
Back in the main query, if the number of orders is zero (isnum(subquery2.NumOrders),0), there aren’t any orders.
That’s one way.
Joe
I use this method by @hackaphreaka all the time and believe it is the simplest to implement in a BAQ. Just create a left join from Customer to OrderHed and then use OrderHed.Company ISNULL in your subquery criteria.
