CODEWITHSUNDEEP
sqlsql-server-2008-r2
Dalal
Dalal

Reputation: 1106

Specific query to select all rows in Table A that don't match criteria defined in rows of Table B?

I'm having trouble building a specific query to select records. Here is a dumbed down example of what I'm trying to achieve.

Let's say I have a table called Criteria that looks something like this:

ID   Unit      Product   Part
==   ========  =======   ========
1    'sports'  'bike'    'handle'
2    'sports'  ' '       ' '
3    'furn'    'couch'   ' '
etc.

And I have a table called Entries that looks something like this:

ID   Tuple
==   =========================
1    'sports / bike / seat'
2    'sports / racket / frame'
3    'furn / desk / leg'
4    'furn / lamp / shade'
etc.

Record 1 in Criteria suggests that any tuple that is "sports / bike / handle" qualifies. Record 2 in Criteria, where the Product and Part contain spaces, suggests that any tuple beginning with 'sports' qualifies ("sports / % / %"). Record 3 suggests that any tuple beginning with 'furn' and 'couch' qualifies ("furn / couch / %"). I want to select all the records that don't qualify for any of the criteria.

In this case, records 3 and 4 from Entries should be returned, because they don't match any of the given criteria.

I'm new to SQL, so I'm having trouble coming up with a suitable query. My thought process is along these lines:

For each record of Criteria, build a string comparison string based on the Unit, Product, and Part. For record 1, for example, the string would be 'sports / bike / handle'. For record 2, the string would be 'sports / % / %', and for record 3: 'furn / couch / %'. Now, select all records where Tuple does not equal any of those strings. I'm having trouble translating this into SQL terms, and that is where I need your help.

Upvotes: 0

Views: 1586

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can do this with an outer join, using like:

select e.*
from Entries e left outer join
     (select c.*,
             ((case when c.unit = ' ' then '%' else c.unit end) + ' / ' +
              (case when c.product = ' ' then '%' else c.product end) + ' / ' +
              (case when c.part = ' ' then '%' else c.part end)
             ) as likestr
      from Criteria c
     ) c
     on e.tuple like likestr
where c.id is null

This will be an expensive operation, but should do what you want.

The data structure would make more sense if you stored the components of the tuple in separate columns. Then you could do a column-by-column comparison.

Upvotes: 2

Nicholas Carey
Nicholas Carey

Reputation: 74277

Why are you storing data like that? Inquiring minds want to know.

However, you might try something along these lines:

select *
from entries e
where not exists ( select *
                   from criteria c
                   where e.tuple like           case coalesce( unit    , '' ) when '' then '%' else unit    end
                                      + ' / ' + case coalesce( product , '' ) when '' then '%' else product end
                                      + ' / ' + case coalesce( part    , '' ) when '' then '%' else part    end
                 )

Ugly, but gits 'er done.

Upvotes: 2

Related Questions