@NateS this is a perfect query to use a CTE for.
@jhecker sorry for hijacking your question, I’m going to answer it, but I also want to add onto Nate’s post. I Still Don't Understand CTEs
So, this is what I would do.
Create your BAQ and make SubQuery1 a CTE. I would group on all the fields to get rid of any duplicate lines. You will end up with an in memory table of:
| Company | EmpNum | JobNum | PartNum |
|---|---|---|---|
| SA | JAMF | 1842529 | 4567890 |
| SA | JAMF | 1842379-CP | C123456 |
| SA | JAMF | 1842566 | 1357924 |
Now add SubQuery2 to your BAQ as a CTE and you will have a second in memory table of:
| Company | UnitPrice | PartNum | ShipDate |
|---|---|---|---|
| SA | 3.61000 | C123456 | 7/15/2022 |
Now, create SubQuery3 as TopLevel and add both SubQuery1 and SubQuery2 as your tables in Phrase Build and join them. Select your Display Fields and you should return:
| Company | EmployeeNum | JobNum | PartNum | Company | UnitPrice | PartNum | ShipDate |
|---|---|---|---|---|---|---|---|
| SA | JAMF | 1842379-CP | C123456 | SA | 3.61 | C123456 | 7/15/2022 |