# BAQ outer join problem? **URL:** \ **Category:** Epicor ERP 10\ **Created:** [September 14, 2018, 2:01pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806 "2018-09-14T14:01:32Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Banderson](https://www.epiusers.help/user_avatar/www.epiusers.help/banderson/32/50875_2.png) [@Banderson](https://www.epiusers.help/u/Banderson)\ **Post date:** [September 14, 2018, 2:01pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/1 "2018-09-14T14:01:32Z")
So I’m trying to make a simple BAQ. I have a part (a pulley) that always requires a bushing to go with it. There are some incorrect BOM’s where the bushing isn’t called out. I want to see where a parent assembly called for one part, but not the other. I should be able to do a full outer join with with the same table like shown. ![image](https://epiusers.help/uploads/default/original/2X/0/0c6388c7ce207d2a677b15994d8510b673d1a785.png) ![image](https://epiusers.help/uploads/default/original/2X/4/4942a9eb64ad25da6885e560310a69f5a493c148.png) ![image](https://epiusers.help/uploads/default/original/2X/8/84aaf0116c3714149cc531878593f91758a028ca.png) However the SQL being generated is doing a where clause on the results, so the outer join isn’t working right. Does anyone know what I am doing wrong or a workaround to make this work? ```auto select [PartMtl].[PartNum] as [PartMtl_PartNum], [PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum], [PartMtl1].[PartNum] as [PartMtl1_PartNum], [PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum] from Erp.PartMtl as PartMtl full outer join Erp.PartMtl as PartMtl1 on PartMtl.Company = PartMtl1.Company and PartMtl.PartNum = PartMtl1.PartNum and ( PartMtl1.MtlPartNum = '603-0-0050' ) where (PartMtl.MtlPartNum = '604-0-0035') ``` Results of that query ![image](https://epiusers.help/uploads/default/original/2X/b/b4628cd9cfe880329c7afb257f0d9c72ab65ebfe.png) What I would expect. ![image](https://epiusers.help/uploads/default/original/2X/8/8df01ee7149517ae26a63f110ed5d91d4156bda2.png) ---
**Author:** ![ckrusen](https://www.epiusers.help/user_avatar/www.epiusers.help/ckrusen/32/50584_2.png) [@ckrusen](https://www.epiusers.help/u/ckrusen)\ **Post date:** [September 14, 2018, 2:30pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/2 "2018-09-14T14:30:32Z")
wait … that wasn’t right ---
**Author:** ![Banderson](https://www.epiusers.help/user_avatar/www.epiusers.help/banderson/32/50875_2.png) [@Banderson](https://www.epiusers.help/u/Banderson)\ **Post date:** [September 14, 2018, 2:57pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/3 "2018-09-14T14:57:06Z")
So it looks I can do a left join, (or a right join if I change the order of the tables) but a full join will not get results both tables unless the rows match. The first table in the list will limit all of the results to existing in that table, and it will show when the second table doesn’t exist, but I can’t get it to go both ways with the full join. Is this a known limitation? With SQL or the BAQ? or is this something that is a bug that I should report. ---
**Author:** ![ckrusen](https://www.epiusers.help/user_avatar/www.epiusers.help/ckrusen/32/50584_2.png) [@ckrusen](https://www.epiusers.help/u/ckrusen)\ **Post date:** [September 14, 2018, 3:13pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/4 "2018-09-14T15:13:12Z")
If you’re doing this as a one-off, you could include a filed from the 2nd table in the displayed columns. Then look for records with that field being blank Here’s the output of BOMs that contain PN1. BOM’s w/o PN2 show as the ones with blanks ParentPart ![image](https://epiusers.help/uploads/default/original/2X/a/a1dcc073343114c05d5aa4d927074f3c95fddfac.png) Edit: if you make the above a Sub-Query, it could then be filtered on records where ParentPart ISNULL ---
**Author:** ![ckrusen](https://www.epiusers.help/user_avatar/www.epiusers.help/ckrusen/32/50584_2.png) [@ckrusen](https://www.epiusers.help/u/ckrusen)\ **Post date:** [September 14, 2018, 3:21pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/5 "2018-09-14T15:21:40Z")
See if this works [ck-test-outer.baq](https://epiusers.help/uploads/default/original/2X/6/69df7e91df6551a876f95f66ea3c991a3a9f628c.baq) (32.6 KB) It’s 3 subs Sub2 (inner) - finds BOM’s with both Parts Sub1 (inner) - finds BOMs With Part A, if BOM doesn’t contain Part B, that field will be null Sub0 (top) - filters Sub1 to show only when Part B is null ---
**Author:** ![Banderson](https://www.epiusers.help/user_avatar/www.epiusers.help/banderson/32/50875_2.png) [@Banderson](https://www.epiusers.help/u/Banderson)\ **Post date:** [September 14, 2018, 3:24pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/6 "2018-09-14T15:24:49Z")
What about when B exists and not A? What you have would basically be a left join, which I can do with one level. (from your description, I haven’t opened up the BAQ yet) I got the information I needed for this specific problem (hence the excel screen shot), But I’m worried about the full join not doing what a fill join should be doing for future BAQ’s. ---
**Author:** ![ckrusen](https://www.epiusers.help/user_avatar/www.epiusers.help/ckrusen/32/50584_2.png) [@ckrusen](https://www.epiusers.help/u/ckrusen)\ **Post date:** [September 14, 2018, 3:26pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/7 "2018-09-14T15:26:37Z")
I would not be surprised If I added unnecessary layers while trying to get to end game, and then forgot what the end game was 🙂 ---
**Author:** ![Banderson](https://www.epiusers.help/user_avatar/www.epiusers.help/banderson/32/50875_2.png) [@Banderson](https://www.epiusers.help/u/Banderson)\ **Post date:** [September 14, 2018, 3:28pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/8 "2018-09-14T15:28:30Z")
I can get this with on level. (I jumped to the job table, because I fixed the master. ![image](https://epiusers.help/uploads/default/original/2X/b/b302b7201a75bf5ee17a854e6805c223192137a3.png) Then if I switch the order of the tables, then I get this. ![image](https://epiusers.help/uploads/default/original/2X/0/01b491830c1217075e157d9ea556f1d36f7dd2ec.png) ![image](https://epiusers.help/uploads/default/original/2X/f/f96464f44465f38bdaa9ede11d32bd4378be5126.png) If I’m not mistaken, the full join should work the same no matter the table order right? ---
**Author:** ![ckrusen](https://www.epiusers.help/user_avatar/www.epiusers.help/ckrusen/32/50584_2.png) [@ckrusen](https://www.epiusers.help/u/ckrusen)\ **Post date:** [September 14, 2018, 3:33pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/9 "2018-09-14T15:33:34Z")
I’m no SQL expert. So I don’t know the ins and outs of a FULL join. Are trying to get BOMs where (has A and Not B) OR (has B and Not A)? ---
**Author:** ![Banderson](https://www.epiusers.help/user_avatar/www.epiusers.help/banderson/32/50875_2.png) [@Banderson](https://www.epiusers.help/u/Banderson)\ **Post date:** [September 14, 2018, 3:35pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/10 "2018-09-14T15:35:31Z")
yeah. They are dependent parts. You can’t have one without the other. So just want to see where there is one without the other. A full join, with criteria _should_ do that, as far as I know. But hopefully someone with more SQL experience can tell me what I should do instead. There are other areas where I want to check for matching things and show me where they don’t match (both ways) and I’m worried that it won’t work the way I expect. @knash Any ideas? ---
**Author:** ![knash](https://www.epiusers.help/user_avatar/www.epiusers.help/knash/32/74179_2.png) [@knash](https://www.epiusers.help/u/knash)\ **Post date:** [September 14, 2018, 3:44pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/11 "2018-09-14T15:44:39Z")
Why not this? create a sq to show job and part A create a sq to show job and part B Then a top query to do the outer join ---
**Author:** ![ckrusen](https://www.epiusers.help/user_avatar/www.epiusers.help/ckrusen/32/50584_2.png) [@ckrusen](https://www.epiusers.help/u/ckrusen)\ **Post date:** [September 14, 2018, 3:50pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/12 "2018-09-14T15:50:26Z")
So in English… BOM’s with (Part A **OR** Part B) - BOMs with (Part A **AND** Part B) That first set of (…) finds any BOM that uses either (which includes both) The second set of (…) is just the BOMs that contain both ---
**Author:** ![Banderson](https://www.epiusers.help/user_avatar/www.epiusers.help/banderson/32/50875_2.png) [@Banderson](https://www.epiusers.help/u/Banderson)\ **Post date:** [September 14, 2018, 3:56pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/13 "2018-09-14T15:56:45Z")
Yeah, so basically, you have to avoid putting table criteria on anything with an outer join, or it will give you problems. If you put the table into a subquery and filter it there first, it will work. So that’s the workaround. Does anyone agree that this isn’t how it _should_ work? Or am I just being overly critical? ---
**Author:** ![Mark\_Wonsil](https://www.epiusers.help/user_avatar/www.epiusers.help/mark_wonsil/32/51594_2.png) [@Mark\_Wonsil](https://www.epiusers.help/u/Mark_Wonsil)\ **Post date:** [September 14, 2018, 4:36pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/14 "2018-09-14T16:36:11Z")
May I suggest another method that includes no joins? Create two calculated fields: NumPartA and NumPartB. These fields are numeric and are equal to 1 if the PartMtl is equal to the selected part (A or B parameters) and 0 (zero) if not. Now, subtotal these by Part and Rev. In the subtotals, if both totals are zero, then you don’t care. If the totals don’t match then you have one part but not another. No links. One run. No errors. Mark W. ---
**Author:** ![ckrusen](https://www.epiusers.help/user_avatar/www.epiusers.help/ckrusen/32/50584_2.png) [@ckrusen](https://www.epiusers.help/u/ckrusen)\ **Post date:** [September 14, 2018, 4:53pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/15 "2018-09-14T16:53:15Z")
> [@Mark\_Wonsil](#): > > No links. One run. No errors. But requires post processing 😉 - to filter out the zeros, no? What if part A is used twice, and part B none? That gives you a count of 2, no? ---
**Author:** ![Mark\_Wonsil](https://www.epiusers.help/user_avatar/www.epiusers.help/mark_wonsil/32/51594_2.png) [@Mark\_Wonsil](https://www.epiusers.help/u/Mark_Wonsil)\ **Post date:** [September 14, 2018, 5:18pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/16 "2018-09-14T17:18:40Z")
No post-processing. Use SubQuery Criteria to check if both calculated fields are non-zero. Remember, there are TWO totals: PartA and PartB, so we’re interested whenever there’s a difference. 2 and 0 is just as wrong as 1 and 0, or 2 and 1. Mark W. ---
**Author:** ![ckrusen](https://www.epiusers.help/user_avatar/www.epiusers.help/ckrusen/32/50584_2.png) [@ckrusen](https://www.epiusers.help/u/ckrusen)\ **Post date:** [September 14, 2018, 5:41pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/17 "2018-09-14T17:41:33Z")
I’m confused with “Create two calculated fields: NumPartA and NumPartB. These fields are numeric and are equal to 1 if the PartMtl is equal to the selected part (A or B parameters) and 0 (zero) if not. **Now, subtotal these by Part and Rev.** ” How am I subtotaling these two values? like: ```auto SUM(case when PartMtl.MtlPartNum = @PartA then 1 else 0 end) ``` And then grouping by PartNum and Rev like: ![image](https://epiusers.help/uploads/default/original/2X/1/1d403cf1c22b28602c0441107d4a3caa75f39db5.png) Or do I need two more calc fields to sum up NumPartA and NumPartB? **Edit:** Adding subquery criteria based on NumPartA and NumPartB generate error: “An Aggregate may not appear in the WHERE clause …” ---
**Author:** ![Mark\_Wonsil](https://www.epiusers.help/user_avatar/www.epiusers.help/mark_wonsil/32/51594_2.png) [@Mark\_Wonsil](https://www.epiusers.help/u/Mark_Wonsil)\ **Post date:** [September 14, 2018, 6:15pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/18 "2018-09-14T18:15:28Z")
Here’s how I would do it. (I did use a subquery, so I’ll have to eat the no-link statement) 😉 [Diff-Test.baq](https://epiusers.help/uploads/default/original/2X/c/c41d3c564be70e779910b31dc6733079742eb99c.baq) (22.7 KB) Mark W. ---
**Author:** ![ckrusen](https://www.epiusers.help/user_avatar/www.epiusers.help/ckrusen/32/50584_2.png) [@ckrusen](https://www.epiusers.help/u/ckrusen)\ **Post date:** [September 14, 2018, 6:20pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/19 "2018-09-14T18:20:58Z")
“Can’t import query definition from version latter than current: 3.2.200.0” ☹ But I get the gist of it now. ---
**Author:** ![Mark\_Wonsil](https://www.epiusers.help/user_avatar/www.epiusers.help/mark_wonsil/32/51594_2.png) [@Mark\_Wonsil](https://www.epiusers.help/u/Mark_Wonsil)\ **Post date:** [September 14, 2018, 6:23pm UTC](https://www.epiusers.help/t/baq-outer-join-problem/47806/20 "2018-09-14T18:23:28Z")
Whoops. Sorry about that. I can send screen shots if that helps! Just let me know. Mark W. [Next page](https://www.epiusers.help/t/baq-outer-join-problem/47806.md?page=2)