title: SQL: How to query items from one table that are not in another when the syntax is not the same?
body: <p>I have a question because I'm really bad at SQL. I understand basic functions but when<br>
  it gets a bit more complex, I'm completly lost.</p>
  
  <p>here is what I have:<br>
  tables: tA, tB<br>
  columns: tA: refA tB: refB<br>
  basically refA and refB represent the same thing (some id of a form like xxx-xxx-xxx), but
  refB can have information appended (like xxx-xxx-xxx_Zxxx or xxx-xxx-xxx Zxxx)</p>
  
  <p>here is what I know how to do:<br>
  querying items that are in a table but not in another (when they are exactly the same)</p>
  
  <pre><code>select refA
  from tA
  where not exists (select *
  from tB
  where tB.refB = tA.refA
  )
  </code></pre>
  
  <p><strong>What i want to do:</strong><br>
  I want a query that will list items from refA that are not in refB.
  BUT, Problem is if I run a "simple" query with a NOT EXISTS like I just showed, it will return everything, 
  because of the appends. so I thought about using some syntax like this:</p>
  
  <pre><code>SELECT refA
  FROM tA
  WHERE NOT EXISTS (SELECT * 
  FROM tB
  WHERE tB.refB LIKE CONCAT(tA.refA,'%'))
  </code></pre>
  
  <p>but... of course, it doesn't work.</p>
  
  <p>Could someone show me how it should be done, and also explain how it works, so I can learn ?<br>
  Thanks in advance !</p>
  
  <p><strong>edit: additional info</strong><br>
  I can't use a left() or something alike, because the ref format is similar but not always the same (varies in number of characters).<br>
  The only way to detect the end of the id before the append, is that there is either a blank space or an underscore.</p>
  
  <p><strong>edit 2: data sample causing problems (MON, Jan. 10th)</strong>  </p>
  
  <p>here is some actual data from the tables, which makes most answers people have given here
  miss some results :/  </p>
  
  <p>in tA:<br>
  B20-60-04-6A-1<br>
  B20-60-04-6A-11<br>
  B20-60-04-6A-12<br>
  B20-60-04-6A-13  </p>
  
  <p>in tB:<br>
  B20-60-04-6A-11_XX<br>
  B20-60-04-6A-12_XX<br>
  B20-60-04-6A-13_XX  </p>
  
  <p>problem with mid(), left(), etc. is that if we check "B20-60-04-6A-1" (14 chars)
  against the 14 first chars, it will return 3 positives, while in fact it is not in tB...</p>
  
  <p>so, how can we proceed ?  </p>
  
  <p>Examples of data patterns in tA are like this:<br>
  (X, XYZ: charaters. x: alphanumerical)<br>
  Xxx-xx-xx-x<br>
  Xxx-xx-xx-xx<br>
  Xxx-xx-xx-xx-xx<br>
  Xxx-xx-xx-xx-xx-x<br>
  etc  </p>
  
  <p>examples of data patterns in tB:<br>
  Xxx-xx-xx-xx-xx-XYZ-xx Z xxx_XX<br>
  Xxx-xx-xx-xx-xx-XYZZxxx_XX<br>
  Xxx-xx-xx-xx-xx Z xxx_XX  </p>
  
  <p>XYZ are always the same 3 characters. When we do not have XYZ, there is always a blank space or an underscore.  </p>
  
  <p>so the string of data we compare should be trimmed according to this:<br>
  - from start to -XYZ string<br>
  - or, if no -XYZ in the string, from start to the first " " or "_"  </p>
  
  <p>I'd write that lightning fast in VBA, but in SQL... well, I'll give it a shot, but I'm really bad at it :D</p>