CODEWITHSUNDEEP
sqlregexpostgresql
1252748
1252748

Reputation: 15372

select record in one table that are not in another with a pattern match

I would like to find all records in the column of one table that are not in a column of another. I can do so with this query:

SELECT
    kywd
FROM
    from_client
WHERE
    kywd NOT IN
        (SELECT
            kywd
        FROM
            from_me);

However, I would like to extend this to allow for some pattern matching. At the very least I'd like to see if the from_client kywd appears anywhere in the from_me kywd. So, like, '%kywd%'. I tried ...WHERE '%' || kywd || '%' NOT IN... as a wild guess, and a result set was actually returned, but I suspect this syntax is just gibberish.

How can I make this select statement allow for these wildcards?

Upvotes: 0

Views: 70

Answers (4)

peter.petrov
peter.petrov

Reputation: 39457

Try something along these lines.

SELECT
    kywd
FROM
    from_client
LEFT OUTER JOIN from_me ON 
from_client.kywd like from_me.kywd -- replace with whatever appropriate
WHERE
from_me.some_id IS NULL

By "replace with whatever appropriate", I mean:

from_client.kywd LIKE '%' || from_me.kywd || '%'

or

from_me.kywd LIKE '%' || from_client.kywd || '%'

or any other condition which is appropriate there.

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125304

not exists is likely to be faster than join

select kywd
from from_client
where not exists (
    select 1
    from from_me
    where position(from_client.kywd, kywd) > 0
)

Upvotes: 1

Bruno
Bruno

Reputation: 641

You could try to use the EXCEPT statement. In you scenarion it would be something like

 SELECT kywd
 FROM  from_client
 --WHERE <Conditions here>
 except
 SELECT kywd
 FROM  from_me
 --WHERE <Conditions here>

and returns what exists in from_client that does not exists in from_me.

Upvotes: 0

Peter G
Peter G

Reputation: 1613

SELECT from_client.kywd
FROM from_client
LEFT JOIN from_me
  ON from_client.kywd LIKE '%' || from_me.kywd || '%'
WHERE from_me.kywd IS NULL

Upvotes: 1

Related Questions