SQL - Get records from one table that aren't "LIKE" in another table

Question

I have two tables: tables: products and posts

table products has field model with values "AH0002", "O-PO-201", "O-PO-304" etc. table posts has field post_title with values like "Product AH0002 is the best", "Red O-PO-201 is really good".

What I need is to display products rows where their name doesn't appear in posts table (in post_title field).

How to do it in mysql?

Can you show what you've got so far? You'll need a `like` condition as well as a `where not exists` condition for sure. Beyond that, we'll really need to see what you already have. — user2366842, Jul 25 '14 at 21:59
@user2366842 you don't NEED a not exists subquery, and it may not perform as well as other options, ie. an outer join or a not in subquery — Brian DeMilia, Jul 25 '14 at 22:08

score 1 · Accepted Answer · edited Jul 25 '14 at 22:09

1

Try:

select pr.*
  from products pr
  left join posts po
    on post_title like concat('%', pr.model, '%')
 where post_title is null

The above assumes mysql syntax, it will vary slightly by database.

SQL Fiddle

edited Jul 25 '14 at 22:09

Nizam

4,569
3
43
60

answered Jul 25 '14 at 22:06

Brian DeMilia

13,103
1
23
33

I had to add only CONVERT to pr.model but it work like a charm. When I remove where clause than I get products that are in both tables ;) – Ryan Jul 26 '14 at 09:33
@Ryan convert would be needed if the field model is a different data type than post_title. Yes the where clause is filtering in on products with no associated posts, the left outer join to the posts table is what allows nulls, so by allowing nulls you can filter in on nulls in the WHERE clause. Also if you wanted all posts w/ no products (the other way around), you can change "left join" to "right join" and change "where post_title is null" to "where pr.model is null" – Brian DeMilia Jul 26 '14 at 12:28

pmbAustin · Answer 2 · 2014-07-25T22:48:57.437

1

I think you want something like this:

SELECT p.*
  FROM products p
 WHERE NOT EXISTS (SELECT 1 FROM posts WHERE post_title LIKE '%' + p.model + '%')

This syntax assumes Microsoft SQL Server.

For MySQL, just change the LIKE clause to be LIKE CONCAT('%', p.model, '%')

edited Jul 25 '14 at 22:48

answered Jul 25 '14 at 22:07

pmbAustin

3,890
1
22
33

The above syntax is for SQL Server – pmbAustin Jul 25 '14 at 22:14
In MySQL `+` doesn´t work to concatante strings - http://stackoverflow.com/questions/19261760/concatenate-string-with-field-value-in-mysql – Nizam Jul 25 '14 at 22:15
Use `Concat` instead - concat('%',p.model,'%') – Nizam Jul 25 '14 at 22:16
As already stated, the above SQL is for SQL Server, and works there. For MySQL just adjust the syntax as required. – pmbAustin Jul 25 '14 at 22:16
1

But it could work in mySQL and attends to the OP requirements. Why not fix the code? It is very simple to do. – Nizam Jul 25 '14 at 22:18

Nizam · Answer 3 · 2014-07-25T22:31:16.633

0

Using LIKE

select *
from products A
where model not in (
   select model
   from posts
   inner join products
      on post_title like concat('%',model,'%')
)

Look at the SQL Fiddle

edited Jul 25 '14 at 22:31

answered Jul 25 '14 at 22:03

Nizam

4,569
3
43
60

SQL - Get records from one table that aren't "LIKE" in another table

3 Answers3