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.

We do a program for cataloging images by assigning keywords (tags) to them.

An image has the following keywords "John", "Jane", "Portrait".

Another image has the following keywords "John", "Jane", "Wedding".

When the user tries to catalog the next image and adds the "John" keyword, we want to appear in the 'Suggested' area the keyword "Jane".

How can we extract (using SQL) the suggested keywords based on the most possible combinations?

Note that Images can have more keywords assigned and in 'Suggested' area is best to have more suggestions.

The relevant table is called TagsTree and it has the following creation DDL:

CREATE TABLE TagsTree(ImageID INTEGER NOT NULL, TagID INTEGER NOT NULL, UNIQUE (ImageID, TagID))

...where ImageID is FK to the photo entity and TagID is FK reference to the keyword entity.

Also, any other hints regarding schema etc. are greatly appreciated.

PS: The DB engine is SQLite3, if it matters.

share|improve this question
add comment

1 Answer

I am assuming that your tags are stored in the "Tag" table, but if not it should be an easy fix for the below SQL. I have not used SQLite3 before, but this should be generic SQL. I used the literal "John," but just replace with a variable for whatever the user enters. Also, if you wanted to limit the count to at least x number then put in a having clause.

select t2.keyword
from TagsTree tt
join Tag t on tt.TagID = t.TagID
join TagsTree tt2 on tt.ImageID = tt2.ImageID
join Tag t2 on tt2.TagID = t2.TagID and t.TagID <> t2.TagID
where t.keyword = 'John'
group by t2.keyword
order by count(*) desc, t2.keyword
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.