1

I have two tables:

Table tablefoo contains a column fulldata.
Table tablebar contains a column partialdata.

I want find a list of tablefoo.fulldata that do NOT have partial matches in tablebar.partialdata.

The following provides a list of tablefoo.fulldata with partial matches in tablebar, but I want the negative of this.

select fulldata from tablefoo
where fulldata like any (select '%' || partialdata from tablebar);

This lists every record in partialdata:

select fulldata from tablefoow
where partialdata not in (select '%' || partialdata from tablebar);

Any idea how to get only the results tablefoo.fulldata that do not contain matches to a leading wildcarded tablebar.partialdata?

I found this link: PostgreSQL 'NOT IN' and subquery which seems like it's headed down the right path, but I'm not getting it to work with the wildcard.

Sure, I could write a script to pull this out of psql and do the comparisons, but it would be much nicer to handle this all as part of the query.

Community
  • 1
  • 1
thatguy
  • 13
  • 3
  • Your version of Postgres, the table definitions (`\d tbl`in psql) and sample data would be in order. Ideally a test case, for instance on sqlfiddle.com. – Erwin Brandstetter May 12 '14 at 20:26
  • Postgres version is 9.1. I'll look at adding an example to sqlfiddle.com. Thanks for the suggestion. – thatguy May 12 '14 at 22:47

1 Answers1

0
SELECT fulldata
FROM   tablefoo f
WHERE  NOT EXISTS (
   SELECT 1
   FROM   tablebar b
   WHERE  f.fulldata LIKE ('%' || b.partialdata)
   );
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • @thatguy: I see that kordirko already posted the same but deleted it after you claimed it did not work. Well, it **should** work. Or the question is wrong somehow. – Erwin Brandstetter May 12 '14 at 20:32
  • I was bummed it was deleted as I was hoping to use it as a starting point for comments. I'm working through my data to make sure there isn't something in that tripping everything. Thanks for your response. I'll post back in a bit. – thatguy May 12 '14 at 22:51
  • It's not you. It's me. It worked the 4th time I unfoobarred it. Thanks to kordirko for the original answer and to Erwin for pointing out how I didn't make good use of the original answer. – thatguy May 13 '14 at 01:12