Is thier anyway using the BAQ tool to do a left join/is null type statement? I’m trying to find jobs that are missing all operations - so there would be a record in JOBHEAD with no match in JOBOPER

SELECT Erp.JobHead.JobNum FROM Erp.JobHead LEFT JOIN Erp.JobOper ON Erp.JobHead.JobNum = Erp.JobOper.JobNum WHERE ((Erp.JobOper.JobNum) Is Null);

Isn’t that what you asked here? [image] Jobs without method or without JobOper records ERP 10 How to I capture records that are in JobHead but no JobOper records? Basically if JobHead is table 1 and JobOper is table 2 - all r…

Yes - Thank you! I couldn’t find it on my search. I still haven’t gotten it working. You would think there would be a simple way within the tool (such as left join/is null) as I can see many uses Do you have the screenshots you mentioned in earlier post on how to get the subquery piece working? …

Just do a left join, then in the subquery criteria, add a criteria for the field that you are looking at to be null. [image] [image]

I’m not looking for a field to be null - I’m looking for no matching record IE - job head record but no joboper record for this jobnum

[image] parichter: IE - job head record but no joboper record for this jobnum That’s the definition of null. If you have a job head record, but no job oper record. If they don’t match, and you do a left join, it will return the row with a null.

For that particular example: Join to tables JobHed and JobOpr then in the Joing Type select all rows from JobHed Then in the subquery criteria you can try to create a filter by selecting JobOpr table and then OprSeq field and in the condition ISNULL

I’ll give it a try - I thought NULL in this case was an empty field - no records in JOBOPER have the field NULL - they just don’t exist it JOBOPER similar statement of WHERE NOT EXISTS I’ll let you know how it goes

That’s what I was trying to do - but since you can’t type in statement - you have to use the tool - I couldn’t figure out how to do it.

I did the sub-query (my first finally) and it worked - Thank you. I now have select [JobHead].[JobNum] as [JobHead_JobNum], [JobHead].[PartNum] as [JobHead_PartNum], [JobOper].[OprSeq] as [JobOper_OprSeq] from Erp.JobHead as JobHead left outer join Erp.JobOper as JobOper on JobHead.Company =…

My preferred way in E10 is to do an OUTER join between jobhead and joboper, then summarize the job head, and have a calculated field called OperCount and make it equal to Count(JobOper.JobNum). THEN you get even more information… you can look for jobs with ZERO operations, ONE operation or even find…

How can i find records in 1 table with no matching record in 2nd table

Epicor ERP 10

Banderson (Brandon Anderson) July 31, 2018, 4:45pm 2

Isn’t that what you asked here?

Topic		Replies	Views
Jobs without method or without JobOper records Epicor ERP 10	4	986	May 5, 2017
BAQ Filter for Null Epicor ERP 10	13	9930	March 10, 2022
BAQ - Where something is NOT in joined table Epicor ERP 10 baq	8	3466	November 22, 2021
ERP 9 trouble with simple BAQ Epicor ERP 9 baq	3	816	January 11, 2022
BAQ Report - How to Show Job Operations with Zero Transactions Epicor ERP 10 baq , jobs , report-builder , baq-report	2	831	November 21, 2019

How can i find records in 1 table with no matching record in 2nd table

Related topics