CODEWITHSUNDEEP
mysql
Kondax
Kondax

Reputation: 83

MySQL Select results which do not contain words from another select

I have two tables like:

-----TABLE1----

1.out_name

2.out_name_log

3.out_name2

4.out_name2_log -

5.out_name3

6.out_name3_log

7.out_name4

8.out_name4_log

and

---TABLE2----

1.name2 -

2.name3 -

I want to select data from TABLE1 if they do not contain any words from TABLE2.

I want the result to show:

out_name

out_name_log

out_name4

out_name4_log

Upvotes: 0

Views: 55

Answers (2)

Ullas
Ullas

Reputation: 11556

Try to use a dynamic sql query to achieve this. But this might be a bad solution if TABLE2 having more number of rows.

Query

set @query = null;
select
group_concat(distinct
    concat(
      'col1 not like ''%',col1,'%'' and '
    ) separator ' '
  ) into @query
from TABLE2 ;

set @query = left(@query,length(@query) - 4);
set @query = concat('select * from TABLE1 where ', @query);

prepare stmt from @query;
execute stmt;
deallocate prepare stmt;

SQL Fiddle

Upvotes: 0

Subin Chalil
Subin Chalil

Reputation: 3660

If you want to select data from a column that are not in another table. You can use LEFT/RIGHT JOINs.

  SELECT TABLE1.*
    FROM
    TABLE1 LEFT JOIN TABLE2 ON TABLE1.`col1`=TABLE2.`col1`
    WHERE TABLE2.`col1` IS NULL

Hope this helps.

Upvotes: 1

Related Questions