Posted on 2011-10-20
This should be easy but cannot get it correct.
I have 2 tables one contains 3 Character Codes and the other contains combos of those 3 characters concatentated together as 6 character codes. So table A hold "ABC" and "DEF", TableB holds "ABCDEF" and "DEFABC". TableB also holds combos that are NOT possible from codes in TableA.
I need SQL that say give me all from TableB where the first 3 chars or last 3 chars of TableA match an entry in TableB. The SQL also needs to only return the row ONCE. So I if I had ABCDEF, althought the join on "ABC" and "DEF" is true it should only be returned ONCE.