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

[bahmpi (Bruce Hard)]

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?

[pawel.malicki (Paweł)]

Hello!
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.

[bahmpi (Bruce Hard)]

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…

[Mark_Wonsil]

Maybe use Partition By Count and look for zero:

Looking for a BAQ using 'HAVING' statement

You might be better off doing a count over partition to get the counts, then you can filter by the count later. If you do it this way, you can leave the detail in the rows. [image] [image]

[bahmpi (Bruce Hard)]

Thanks Mark, I’ll give it a try.

[Joseph_Martin]

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.

[bahmpi (Bruce Hard)]

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