BAQ to return only ProjectID's that are not linked to a Sales Order

It seems like this one would be so easy and I’m sure I’m missing something simple. But, I just can’t seem to get the BAQ to do return only Project ID’s that are not linked to any Sales Orders. Any suggestions?

I would try to outer join Project with OrderDtl and then add a condition to display records where Sales Order is null/empty or 0.

Hi Pawel,
I actually tried an outer join from Project to OrderDtl and a condition on the OrderDtl table where Project ID is null/empty. I just tried 0 and that didn’t work either. I also tried using NOT ProjectID = ProjectID in my join between the two tables to no avail. I will keep plugging away at it. Thanks for your input! I know there is something really simple I need to do, it’s just evading me at the moment…

Maybe use Partition By Count and look for zero:

Thanks Mark, I’ll give it a try.

You can alse create a top level query with the Project table, and a subquery with order detail and project ID displayed. Then in the top level at a criteria to the Project table on Project ID with a NOT IN subquery.ProjectID criteria.

1 Like

Ah ha! It looks like that did the trick! Thank you Joseph_Martin!

1 Like