CODEWITHSUNDEEP
mysqljoin
Dingo
Dingo

Reputation: 341

Selecting rows that do not match criteria of inner join

I have two tables, one that holds lot of links (tvlinks), and another that holds list of substrings to be matched against the name of the links (tvshows):

tvlinks:
  id      (int 11) - primary index key
  name    (text)   - name of the tv show link
  link    (text)   - link to a website with information about the tv show

tvshows:
  id      (int 11) - primary index key
  name    (text)   - name of the tv show
  match   (text)   - substring to be matched against name in tvlinks table

there can be multiple rows in tvlinks with exactly the same name and different link. There is no duplicate rows.

i use the following query to get all rows from tvlinks where tvshows.match is a substring of tvlinks.name:

SELECT l.id,
       l.name,
       l.link
    FROM tvlinks tvl
  INNER JOIN tvshows tvs
  ON ( INSTR(tvl.name, tvs.match )
  ORDER BY tvl.name
;

and it works fine, my problem is that i'd like to put together another query that would return only the rows that do not get matched by the query above.

i've been bashing my head on the keyboard on and off for a week or two, and i'm sure its something really simple that i'm missing in a big way. :)

Thanks for your help

Upvotes: 2

Views: 2227

Answers (2)

Michael
Michael

Reputation: 10474

select id from tvlinks where id not in (select l.id, FROM tvlinks tvl INNER JOIN tvshows tvs ON ( INSTR(tvl.name, tvs.match ) ORDER BY tvl.name ;)

Not efficient per se, all depends on your tables.

Upvotes: 1

Reflective
Reflective

Reputation: 3917

INSTR() - Return the index of the first occurrence of substring - not the condition you need

Upvotes: 0

Related Questions