Sign in to follow this  
GenuineXP

SQL Query: Finding Matching Tags

Recommended Posts

I'm just getting started using SQL databases to store data for my applications. I eventually want to use a database (MDF) to store some information for a small text-based game for fun. (XML would work too, but I wanted to give SQL a try.) Anyway, I'm working on a C# application that organizes photos with attributes (width, height, file size, etc.) and tags. There are three tables in my database to accommodate this: Images, Tags, and ImageTags. Images holds records of images (duh), the most important fields being 'Location' and 'ImageID', which is the primary key. Tags contain the tags used to tag images, and include 'Name' and 'TagID' fields. The ImageTags tables relates the two and stores ImageIDs and TagIDs. This works fine, but so far I can't figure out how to look up (i.e. SELECT) images with all the matching tags of a query. I can, however, look up images that contain at least one of the tags in query like this:
SELECT Location FROM Images WHERE ImageID IN
    (SELECT DISTINCT ImageID FROM ImageTags WHERE TagID IN
    (SELECT DISTINCT TagID FROM Tags WHERE Name = 'logos' OR Name = 'flowers'))
This statement successfully gets the location of all images in the database with the "logos" or "flowers" tags, but not the "logos" and "flowers" tags. How can I do this? I've accomplished the same thing using INNER JOIN. Should I use joins to accomplish the AND logic I'm going for? Thanks!

Share this post


Link to post
Share on other sites
One way to achieve what you want would be to construct your query using the INTERSECT keyword:


SELECT Location FROM Images WHERE ImageID IN
(SELECT DISTINCT ImageID FROM ImageTags WHERE TagID IN 'logos')
INTERSECT
(SELECT DISTINCT ImageID FROM ImageTags WHERE TagID IN 'flowers')
INTERSECT
(SELECT DISTINCT ImageID FROM ImageTags WHERE TagID IN 'pr0n')
-- ....and so on


Share this post


Link to post
Share on other sites
you can implicate a JOIN using something like:

SELECT DISTINCT image.Location AS 'Location'
FROM
Images AS image,
Tags AS tag1,
Tags AS tag2,
ImagesTags AS it1,
ImagesTags AS it2
WHERE
image.ImageID = it1.ImageID
AND image.ImageID = it2.ImageID
AND tag1.TagID = it1.TagID
AND tag2.TagID = it2.TagID
AND tag1.Name = 'logos'
AND tag2.Name = 'flowers';

I'm not sure if that DISTINCT is actually required.

[EDIT: So It's the weekend in NZ, had a bit of time..tested the SQL and it works, schema is below


CREATE TABLE images (
ImageID int(11) NOT NULL auto_increment,
Location varchar(255) NOT NULL default '',
PRIMARY KEY (ImageID)
);

INSERT INTO images VALUES
(1,'/usr/home/Porn/1.mpg'),
(2,'/usr/home/Porn/2.mpg'),
(3,'/usr/home/Porn/3.mpg'),
(4,'/usr/home/Flowers/f1.jpg'),
(5,'/usr/home/Flowers/f2.jpg'),
(6,'/usr/home/Flowers/f8.jpg'),
(7,'/usr/home/Trees/tree1.jpg'),
(8,'/usr/home/Trees/tree3.jpg');

CREATE TABLE imagestags (
ImageID int(11) NOT NULL,
TagID int(11) NOT NULL,
PRIMARY KEY (ImageID,TagID),
FOREIGN KEY (ImageID) REFERENCES images (ImageID),
FOREIGN KEY (TagID) REFERENCES tags (TagID)
)

INSERT INTO imagestags VALUES
(1,1),(2,1),(3,1),
(4,2),(5,2),(6,2),
(7,2),(8,2),
(7,3),(8,3);

CREATE TABLE tags (
TagID int(11) NOT NULL auto_increment,
Name varchar(32) default NULL,
PRIMARY KEY (TagID)
);

INSERT INTO tags(TagID, Name) VALUES
(1,'Porn'), (2,'Flowers'), (3,'Trees');



The SQL query produces the desired output - the last images in /usr/home/Trees are taged as Flowers (TagID=2) and as Trees (TagID=3), the SQL query produces the 2 tree images as expected.

I need a hobby.

Regards,
-Danu

[Edited by - silvermace on March 16, 2007 6:49:28 PM]

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this