Compatible with many SQL standards

Question

I am implementing a tag system for a website. For each object there are several tags and several objects for each tag. This is achieved by storing a table with two values ​​for each record, one for the object identifiers and the tag.

I want to write a query to find the objects matching the specified set of tags. Suppose I had the following data (in the format [object] → [tags] *)

apple -> fruit red food
banana -> fruit yellow food
cheese -> yellow food
firetruck -> vehicle red

If I want to match (red), I have to get an apple and shoot. If I want to match (fruit, food), I have to get (apple, banana).

How to write a SQL query to do what I want?

@Jeremy Ruten,

Thanks for your reply. The notation used was used to provide some sample data. There is a table in my database with 1 object identifier and 1 tag per entry.

Secondly, my problem is that I need to get all the objects matching all the tags. Substituting your OR for AND like this:

SELECT object WHERE tag = 'fruit' AND tag = 'food';

The result does not work at startup.

Answer 1

Given:

• object table (primary key identifier)
• objecttags table (foreign keys objectId, tagid)

• tag table (primary key identifier)

  SELECT distinct o.*
    from object o join objecttags ot on o.Id = ot.objectid
                  join tags t on ot.tagid = t.id
   where t.Name = 'fruit' or t.name = 'food';
  

, , , 2 , , , , . , 1 .

, group by having count = <number of ors> .

  SELECT distinct o.name, count(*) as count
    from object o join objecttags ot on o.Id = ot.objectid
                  join tags t on ot.tagid = t.id
   where t.Name = 'fruit' or t.name = 'food'
group by o.name
  having count = 2;

Answer 2

, , , , .

SQL - :

1) Tags ( tag_id, name )
2) Objects (whatever that is)
3) Object_Tag( tag_id, object_id )

, , , ( ). , ( , ).

, , SQL :

:

    SELECT obj 
      FROM object
     WHERE EXISTS( SELECT * 
                     FROM tags 
                    WHERE tag = 'fruit' 
                      AND oid = object_id ) 
       AND EXISTS( SELECT * 
                     FROM tags 
                    WHERE tag = 'Apple'
                      AND oid = object_id )

, :

SELECT oid
  FROM tags
 WHERE tag = 'Apple'
INTERSECT
SELECT oid
  FROM tags
 WHERE tag = 'Fruit'

Answer 3

@Kyle: :

SELECT object WHERE tag IN ('fruit', 'food');

, , , , , .

Answer 4

Steve M. , , :

select object
from tblTags
where tag = @firstMatch
and (
       @secondMatch is null 
       or 
       (object in (select object from tblTags where tag = @secondMatch)
     )

Now it does not scale very well, but it will get what you are looking for. I think there is a better way to do this so that you can easily have N number of matching elements without significantly affecting the code, but this is currently eluding me.

Answer 5

I recommend the following scheme.

Objects: objectID, objectName
Tags: tagID, tagName
ObjectTag: objectID,tagID

With the following query.

select distinct
    objectName
from
    ObjectTab ot
    join object o
        on o.objectID = ot.objectID
    join tabs t
        on t.tagID = ot.tagID
where
    tagName in ('red','fruit')

Answer 6

I would suggest that there be 1 tag per entry in your table, for example:

 apple -> fruit
 apple -> red
 apple -> food
 banana -> fruit
 banana -> yellow
 banana -> food

Then you could just

 SELECT object WHERE tag = 'fruit' OR tag = 'food';

If you really want to do it your way, you can do it like this:

 SELECT object WHERE tag LIKE 'red' OR tag LIKE '% red' OR tag LIKE 'red %' OR tag LIKE '% red %';