0

I would like to compare table1 and table2 with specified column as string and want to return unmatched records from table1.

It works great when I use other way just 'like' and return matching result.

But I am really looking to get unmatched records.

Here is the sample tables 

table 1
----------------------------------
No.     DesignID
1       c12345
2       c16
3       c20

    Table 2
    ----------------------
    No.    DesignOption
    1      Online-c12345-print
    2      Online-c16-proof


   
 $db->fetchallColumn(SELECT distinct(a.DesignID) FROM table1 as a, table2 as b where b.DesignOption 
    not like CONCAT('%', a.DesignID, '%'));

with join example

$db->fetchallColumn(SELECT distinct(a.DesignID) FROM table1 as a inner join 
table2 as b on b.DesignOption not like CONCAT('%', a.DesignID, '%'));

Expected result: c20

Instead I get all the records from table1

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Bagan
  • 33
  • 7
  • You need to `join` table1 to table2. Right now you're selecting everything from table1 (it's not currently restricted either by a where clause, or table2) – RToyo Jul 25 '17 at 17:49
  • @RobbieToyota thanks for your quick response, I have tried some join queries but I get same result. I will add that in my question.. – Bagan Jul 25 '17 at 17:51
  • Looking at your update, it doesn't look like you've completed the join statement. Which column in table a, does b.DesignOption correspond to? You should have something like this: `SELECT [...] FROM table1 as a JOIN table2 as b ON b.DesignOption=a.CorrespondingColumn`. – RToyo Jul 25 '17 at 18:00
  • Use a subquery using `where Like` as you stated above works, and then outer query `where not in subquery`. Also, wouldn't the expected results be `c19 and c20`? – SS_DBA Jul 25 '17 at 18:03
  • @RobbieToyota.. I do not have matching indexes to use equal to operation, so I have used search pattern to get unmatched records. please refer the tables and give me your suggestion. – Bagan Jul 25 '17 at 18:05
  • @WEI_DBA... sorry I have updated the table records.. should be c20. – Bagan Jul 25 '17 at 18:06
  • Very good. But still look at my comment about subquery. – SS_DBA Jul 25 '17 at 18:07
  • @WEI_DBA.. would you mind to give me some sample? – Bagan Jul 25 '17 at 18:08
  • Gordon beat me to it... His was my next suggestion. Simpler and easier to code. – SS_DBA Jul 25 '17 at 18:08
  • Will DesignOption allways follow a format of x-DesignID-y? If so, you should be able to use a string splitting function to `JOIN` without using a `NOT LIKE %x%`. – Shawn Jul 25 '17 at 19:00
  • @Shawn.. Glad to know the several options, would you mind to show up the option or reference link for string splitting functions. Thanks for your overview. – Bagan Jul 25 '17 at 19:10
  • @Bagan I don't primarily work with MySQL, so I don't know if there's a better option in newer versions. This link (https://stackoverflow.com/questions/2696884/split-value-from-one-field-to-two) is older, but it should provide a good starting point. The first answer by Daniel Vassallo. – Shawn Jul 25 '17 at 19:14

3 Answers3

3

Assuming your query works, just use an outer join:

SELECT DISTINCT a.DesignID
FROM table1 a left join 
     table2 b 
     ON b.DesignOption like CONCAT('%', a.DesignID, '%')
WHERE b.DesignOption IS NULL;

Note that DISTINCT is not a function. The SQL operator is SELECT DISTINCT. Don't use parentheses -- unless you have a reason for doing so.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

You may try it this way 

SELECT DISTINCT b.DesignID
FROM table2 a RIGHT JOIN 
table1 b 
ON a.DesignOption NOT LIKE CONCAT('%', b.DesignID, '%')
WHERE a.DesignOption IS NOT NULL;

Hope it will work

vjy tiwari
  • 843
  • 1
  • 5
  • 17
  • My personal belief is that `RIGHT JOIN`s were invented by aspirin companies to boost revenue. :-) In most cases, a `RIGHT JOIN` can be rewritten as a `LEFT JOIN` to make more sense out of the data and produce slightly better query plans. Also, a `NOT LIKE` will be very heavy on the query optimizer. – Shawn Jul 25 '17 at 18:54
0

But if you want to see the subquery approach.

Select DesignID From Table1
Where DesignID not in (SELECT a.DesignID 
FROM table1 as a inner join 
table2 as b on b.DesignOption like CONCAT('%', a.DesignID, '%'))
SS_DBA
  • 2,403
  • 1
  • 11
  • 15