1

I'm having trouble figuring out how to ask SQLite to return all entries in a table where "column" contains text found in the results of a select statement.

I would like it to work a little like this, if this makes any sense:

select * from table1`
where ("%" || (column) || "%") like in
("%" || (select different_column from table2) || "%")`

Any help would be much appreciated. Please let me know if anyone needs more information.

EDIT: My apologies, I have edited the formatting to be more readable and have added more specifics below. I have also deleted my ridiculously unreadable comments in favor of just editing this question.

table1 (alerts) contains a column called "lookup_info", which stores a string. The string generally looks like this: 

--- 
:account_description: Administrator 
:alertable_class: Computer 
:alertable_name: exampledotnet 
:network_account_type: 
:wmi:
:user: exampledomain\administrator

table2 (devices) contains a column called "name", which stores a string. The string generally looks like this:

so-ws-rm101

What I would like to do is return all entries in table1 (alerts) where table1's "lookup_info" column does not contain somewhere within it a computer name stored in table2 (devices) under table2's "name" column.

Basically, there are some alerts that reference computer names that no longer exist. I want those alerts.

Thank you.

EDIT: I have created an fts3 table by the name of alerts_fts that contains the lookup_info column of all entries within the alerts table. I'm trying my search again using full text search this time. This is the query I am using:

select *
  from alerts_fts 
  where alerts_fts.lookup_info match ( select group_concat(name, " OR ") 
                         from Devices
                         group by "x")

This should make the query look like:

select *
  from alerts_fts 
  where alerts_fts.lookup_info match (cw-ws-example OR so-p2-132-example OR 
                         ... OR so-p4-lastexample)
                         group by "x")

Naturally, this is still not working. The query returns nothing. What am I doing wrong?

Kermit
  • 33,827
  • 13
  • 85
  • 121
user2069730
  • 11
  • 1
  • 3

2 Answers2

2

I think you need to do this with a join:

select t1.**
from table1 t1 join
     table2 t2
     on t1.column like '%'||t2.column||'%'

If there is the possibility of more than one match, you'll want to add distinct:

select distinct t1.*
. . .

Perhaps this is what you need:

select t1.**
from table1 t1 join
     table2 t2
     on t1.column like '%'||t2.columnA||'%' or
        t1.column like '%'||t2.columnB||'%' or 
        t1.column like '%'||t2.columnC||'%' or 
        . . .

Based on your edits, I think you want a left outer join and then a check for no match:

select table1.*
from table1 left outer join
     table2
     on t1.column like '%'||t2.column||'%'
where t2.column is null
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Unfortunately, that still doesn't seem to work. Thank you for your persistence. This issue is starting to bother me. – user2069730 Mar 01 '13 at 18:40
  • I edited my question again, just added a line beginning with "basically," thanks again for your help. – user2069730 Mar 01 '13 at 19:41
  • Hi again Gordon and @sgeddes. Once again I have edited my post to include what approach I am working on now. Any help would be much appreciated. – user2069730 Mar 15 '13 at 18:13
1

You can't use Like with In, but you could do something like this assuming you want to match either column back:

Select Distinct table1.* 
From table1 
  Join table2 On 
    column1 like '%'||different_column||'%' 
    Or different_column like '%'||column1||'%'

SQL Fiddle Demo

sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • Thank you @sgeddes for your reply. Unfortunately that does not return the desired information. I have edited my question and added more information. – user2069730 Feb 27 '13 at 19:55