MS Query - outer join on column 1 LIKE column 2?

Question

Can anyone help me create a left outer join statement to work in MS Query?

Based on Hogan's answer, and this MSDN article, I've edited my SQL statement to this (below) but MS Query is still not happy.

SELECT CO.MATERIALS1 AS 'Material',
       CO.`SIZES#1` AS 'Size', 
       CO.`TOOLS#1` AS 'Tool',
       IR.`BODY /JAW` AS 'BodyJaw', 
       IR.PN AS 'PartNo'
FROM {oj `COMBINATIONS$` CO 
LEFT OUTER JOIN `'INSERTS REVIEW$'` IR 
ON [IR.TOOL LIKE '% ' CO.`TOOLS#1` ' %']
      AND [IR.SIZE LIKE '% ' CO.`SIZES#1` ' %']
      AND [IR.MATERIAL  LIKE '% ' CO.MATERIALS1 ' %']}

The syntax for MS Query seems to be slightly different than standard SQL / T-SQL that I've worked with in the past, and I'm struggling to figure out what it wants.

The query above gives me this error about expecting a join operator:

I tried removing the curly braces { } and it complains about table oj:

I tried removing the oj reference and it complains about invalid bracketing:

And in frustration I tried removing all brackets, and that makes it complain about a missing operator syntax error:

Aside from that, I've tried quite a few variations on structure, escape characters, concatenation characters, etc. I feel like I'm just spinning my wheels here with MS Query, and hoping that someone more versed in the nuances of MSQ could point out where I'm going wrong, and help me make it right.

To be clear, here is what I'm looking to achieve.

Given these two tables:

I want to do a left join on the CO table, matching size, tool, and material on the IR table:

Which should yield this result:

Also, I realize this will return a match for "P12" on "P12" and "JP12" which is not really the desired behavior, but I feel I can work that out once I get this basic query working. If need be, I could probably add a leading or trailing "/" to every record in that field, so I can then match on "LIKE '%/'" or something like that.

The criteria field looks fine, why do you think it is wrong? — Hogan, Aug 19 '16 at 18:25
It is not giving the expected results. It's adding the part number where there is no match. — CBRF23, Aug 19 '16 at 18:28
Ok, more detail is needed -- what are the values of the criteria fields -- what is the full list of all criteria you are using etc. We can't guess. — Hogan, Aug 19 '16 at 18:31
Also in your subject you say this is an outer join, but it isn't -- do you want an outer join? — Hogan, Aug 19 '16 at 18:37
Hi hogan - the SQL statement lists my full criteria. What I want is a left outer join - returning all records from the COMBOS table and only records from the INSERTS table that match on TOOL, SIZE, and MATERIAL with the combos table. I think the pictures do a better job explaining. Hopefully that makes sense. I could write exactly what I want in TSQL if that is more helpful? I know how to write that, but MS Query seems to have its own quirks in syntax I'm not really sure how to address. — CBRF23, Aug 19 '16 at 18:44
The SQL statement you show is an inner join not a left / outer join. — Hogan, Aug 19 '16 at 19:55

score 2 · Answer 1 · edited Aug 22 '16 at 17:16

2

Here is an "outer" join notice the criteria are part of the join not part of the where.

SELECT CO.MATERIALS1 AS 'Material',
       CO.`SIZES#1` AS 'Size', 
       CO.`TOOLS#1` AS 'Tool',
       IR.`BODY /JAW` AS 'BodyJaw', 
       IR.PN AS 'PartNo'
FROM `COMBINATIONS$` CO
LEFT JOIN `'INSERTS REVIEW$'` IR 
       ON [IR.TOOL LIKE '% ' CO.`TOOLS#1` ' %'] 
      AND [IR.SIZE LIKE '% ' CO.`SIZES#1` ' %'] 
      AND [IR.MATERIAL  LIKE '% ' CO.MATERIALS1 ' %']

edited Aug 22 '16 at 17:16

Evaldas Buinauskas

13,739
11
55
107

answered Aug 19 '16 at 20:03

Hogan

69,564
10
76
117

Hogan - this looks good from an SQL standpoint, but MS Query doesn't seem to like it. I'm getting an error when I try to enter it in MS Query "Could not add the table 'LEFT'" - I did find some documentation on LEFT OUTER JOIN in ms query. It's looking for `{oj table left outer join table on criteria}` which I've tried to satisfy, but then it gives me more errors about "expected join operator" – CBRF23 Aug 19 '16 at 20:50
@CBRF23 - It is giving you that error because you have a brace character `{` in yoru query. Take out the brace character -- they aren't used in SQL – Hogan Aug 20 '16 at 17:01
I don't think MS Query uses normal SQL. I tried removing brackets, it says can't add table oj. When I tried removing oj, it tells me invalid bracketing of [IR.TOOL LIKE '% ' CO.`TOOLS#1` ' %']` and when I try removing brackets, then it tells me "Syntax error [missing operator] in the expression 'IR.TOOL LIKE '% ' CO.`TOOLS#1` ' %'" – CBRF23 Aug 22 '16 at 11:10
Issue is trailing comma in `FROM` line :) – Evaldas Buinauskas Aug 22 '16 at 17:16

Brian · Answer 2 · 2016-08-22T18:22:40.750

1

will this work: EDIT

SELECT CO.MATERIALS1 AS 'Material',
       CO.`SIZES#1` AS 'Size', 
       CO.`TOOLS#1` AS 'Tool',
       IR.`BODY /JAW` AS 'BodyJaw', 
       IR.PN AS 'PartNo'
FROM {oj `COMBINATIONS$` CO 
LEFT OUTER JOIN `'INSERTS REVIEW$'` IR 
ON [IR.TOOL LIKE '%' + CO.`TOOLS#1` + '%']
      AND [IR.SIZE LIKE '%' + CO.`SIZES#1` + '%']
      AND [IR.MATERIAL  LIKE '%' + CO.MATERIALS1 + '%']}

edited Aug 22 '16 at 18:22

answered Aug 22 '16 at 16:54

Brian

2,078
1
15
28

score 1 · Accepted Answer · answered Aug 22 '16 at 20:52

1

Below ms query will give you the exact result.I have tried with the sample data and the screen shots are attached.

SELECT CO.TOOL, CO.Material, CO.SIZE, IR.PN  , IR.`BODY/JAW`
FROM `G:\test1.xlsx`.`COMBINATIONS$` CO
 LEFT JOIN  `G:\test1.xlsx`.`'INSERTS REVIEW$'` IR
 ON CO.TOOL=IR.TOOL
 AND ((IR.MATERIAL Like '%'+CO.Material+'%')) 
 AND ((IR.SIZE Like '%'+CO.Size+'%'))

Test data is shown in the below screen shots.

Following screen shots contains the ms query and result you want.

answered Aug 22 '16 at 20:52

Unnikrishnan R

4,965
1
12
21

Hmmm. Strange - I get the same results with this as my answer above, which is that material only matches on one value. I think I need to start a separate question for that, as this one seems to have multiple working solutions now. This is the first answer that actually worked without me having to make modifications. – CBRF23 Aug 23 '16 at 11:15
Figured it out with some more SO community help. Man I love this place. http://stackoverflow.com/q/39108919/4106771 – CBRF23 Aug 25 '16 at 21:58
Great man.. Actually now only I saw your new post .. Otherwise I should also make a try... :) Anyway I am happy to see that you got your result.. – Unnikrishnan R Aug 26 '16 at 03:45

score 0 · Answer 4 · edited Mar 20 '17 at 10:29

0

Credit to Hogan and Brian both.

I had to do a little tweaking, but ended up with the following semi-working query. Semi-working in the sense that MS Query is happy, and created the expected resulting table, but is not matching (or returning) anything from the right table. (all rows are null)

Not sure if I should mark this question as answered, and start a new question about why it's not returning the expected data or not.
This has turned into a bit of a chameleon question, but it's also not technically answered yet (the code doesn't work as expected).

As far as what I've got - Hogan was correct that the braces {} needed to be removed. I also found I had to remove all of the brackets [].

Brian was correct in the concatenation character + being needed.

So I ended up with this semi-working SQL statement:

SELECT CO.MATERIALS1 ,
       CO.`SIZES#1` , 
       CO.`TOOLS#1` ,
       IR.`BODY /JAW` , 
       IR.PN 
FROM  `COMBINATIONS$` CO 
LEFT OUTER JOIN `'INSERTS REVIEW$'` IR 
ON IR.TOOL LIKE '%' + CO.`TOOLS#1` + '%'
      AND IR.SIZE LIKE '%' + CO.`SIZES#1` + '%'
      AND IR.MATERIAL  LIKE '%' + CO.MATERIALS1 + '%'

edited Mar 20 '17 at 10:29

Community

1
1

answered Aug 22 '16 at 17:54

CBRF23

1,340
1
16
44

Here's just a shot in the dark, but I wonder if that's because you have a space character in the quoted wildcard character. There doesn't seem to be a space in your examples above. In other words, you're asking it to look for `anything` `(space)` `column` `(space)` `anything`. Try my edit above. – Brian Aug 22 '16 at 18:21
Hi Brian, good thought - I noticed that too. Tried it but still not getting any match. :( – CBRF23 Aug 22 '16 at 18:22
This is some sort of data issue I can't figure out. The TOOL and SIZE filters work perfectly (i.e. "%P12%" matches "P12/JP12", etc.), but MATERIAL won't match any partials (i.e. "%SS3%" is not seen as a match for "SS3/SS4"). This is very strange to me. – CBRF23 Aug 22 '16 at 20:22
Actually, it looks like it's only returning records for one material (only records for 333 material, it ignores everything else). Very curious indeed. – CBRF23 Aug 22 '16 at 20:35
Try putting single quotes around MATERIALS as `CO.\`MATERIALS1\``. You have them around the other two columns and yet they don't contain spaces (although I thought square brackets were for that, but it's worth a try). :-) – Brian Aug 23 '16 at 11:20
I think you want `OR` not `AND` in the filter clauses... this is looking for things with a match on all 3... you just need a match on one. – Hogan Aug 23 '16 at 14:38
@Hogan - I want a match on all three fields, so need to use AND. It can be a partial match (i.e. JP16 on JP16/JP32) which is why I needed LIIKE. Behavior is fine with filters for TOOL and SIZE, even in combination (i.e. both TOOL and SIZE filters in the ON clause). For some reason though, when the MATERIAL filter doesn't work right. Even just by itself (i.e. removed TOOL and SIZE filters entirely and just had MATERIAL filter), it only returns exact matches for one value. I can't figure it out. I tried deleting that value entirely from the material choices, and it returns nothing. – CBRF23 Aug 23 '16 at 14:46
@CBRF23 I notice that material has `/` are you correctly escaping this character -- it can have special meaning. – Hogan Aug 23 '16 at 17:57
@Hogan - in the "inserts" table data? I have forward slash '/' in sizes too, but that one works without issue. I thought only backslash '\' was reserved? Anyways, I just did an experiment and changed all forward slashes to a hyphen '-' and even tried just a comma ',' and still got the same results with material only matching on one value. – CBRF23 Aug 23 '16 at 18:06
Seems like it was a stupid excel cell formatting issue. See Cory's response to [this question](http://stackoverflow.com/q/39108919/4106771) – CBRF23 Aug 23 '16 at 19:17

MS Query - outer join on column 1 LIKE column 2?

Can anyone help me create a left outer join statement to work in MS Query?

To be clear, here is what I'm looking to achieve.

4 Answers4