Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What I'm looking for is a way of verifying that all of a list of items (let's say 'FOO', 'BAR' and 'BAZ') are all in a given table.column. This would be something like SELECT something FROM sometable WHERE somevalue IN ('THIS', 'THAT', 'OTHER THING') ... except it's sort of the inverse:

Ideally the statement would give me everything from ('FOO', 'BAR', 'BAZ') NOT IN sometable.somecolumn then I could use that as my check (assert that my SQL query returns an empty set or take the resulting set and complain that each of these is an "unrecognized" key). (A brute force approach would be to insert all of the terms/tags/items into a temporary, single column table and perform a JOIN ON my actual table's column for the set of rows which are NOT IN it.

I'm sure I'm missing some syntactic trick that'll seem obvious once I see an example.

In this particular case my table structures are something like:

CREATE TABLE items (id     INTEGER PRIMARY KEY, item TEXT UNIQUE NOT NULL);

CREATE TABLE tags  (tag_id INTEGER PRIMARY KEY, tag  TEXT UNIQUE NOT NULL);

CREATE TABLE item_tag (item_id integer, tag_id integer,
                               FOREIGN KEY(item_id) REFERENCES items(id),
                               FOREIGN KEY(tag_id) REFERENCES tags(tag_id),
                               PRIMARY KEY (item_id, tag_id));

... but I'm simply trying to come up with a template for taking an arbitrary number of "tags" and ensure that all of them are valid entries the "tags.tag" column. (The application will have to support arbitrarily complex queries for subsets of these tags to return DISTINCT subsets of the items; but I want to raise an error on any non-existent tag before building the JOIN's WHERE expression).

Obviously I could just loop over the tags doing a separate SELECT tag_id FROM tags where tag=? ... entailing numerous round trips to the database. But this seems silly when I could send all of them to some sort of query in a single statement.

share|improve this question
3  
Confused by the text. The NOT IN sentence seems to ask for something different than the rest of the question. perhaps some example data and desired results would clarify. –  Martin Smith Jun 11 '13 at 9:01
1  
Please show us the table definition together with some sample data and expected output. –  a_horse_with_no_name Jun 11 '13 at 9:05
    
BTW: this example is just from some prototyping in SQLlite3 ... the actual application will be on PostgreSQL but should, ideally, but portable to MySQL as well. –  Jim Dennis Jun 11 '13 at 9:40
2  
I think that what you want is Relational Division but the wording is far from clear. Edit the question with a small sample (of rows in the tables) and wanted output. See a question at SO: How to filter SQL results in a has-many-through relation –  ypercube Jun 11 '13 at 10:02
add comment

3 Answers

If I understand what you're trying to achieve, then I think what you want is a CTE with a UNION list of all the possible tags you want to compare against, then RIGHT JOIN the tag table against the CTE.

Example -

WITH ListOfTags (Tag) AS (SELECT 'Foo' UNION SELECT 'bar' UNION SELECT 'other' UNION SELECT 'thing')
SELECT  *
FROM    item_tag    i
INNER JOIN  tags    t
    ON i.tag_id = t.tag_id
RIGHT JOIN ListOfTags   l
    ON  l.Tag like t.tag
WHERE   i.item_id is null

This might not be the most elegant way of doing it, but it might work for you

share|improve this answer
add comment

I wouldn't call this syntactic sugar by any means, but in Oracle you could pass your tags as a delimited list in a query like this:

SELECT a.Data FROM
(
   SELECT SUBSTR(main_string, position_from + 1, position_to - position_from - 1) Data
   FROM
      (
         SELECT main_string, DECODE(rownum - 1, 0, 0, instr(main_string, ',', 1, rownum - 1))
            position_from, instr(main_string, ',', 1, rownum) position_to
         FROM (SELECT 'FOO,BAR,BAZ,' main_string FROM dual)
            CONNECT BY LEVEL <= LENGTH(main_string)
      )
   WHERE position_to > 0`
) a
WHERE NOT EXISTS (SELECT 1 FROM Tags WHERE Tag = a.Data)
;

Fiddle

share|improve this answer
add comment

Not without some dynamism.

The data set you want to select from is the data you are providing it as a parameter. Not going to happen.

You are going to have deal with it procedurally yourself. If that is literally your use case, there will only ever be a handful of items in the list. There's not much reason to do the whole operation in the db. The pattern I would suggest is to loop over the tags in your app and make a DB call to ValidateTag and pass it a single parameter. The SQL could be as simple as :

select count(*) where tag.tag = <x>
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.