Can MySQL's COUNT function be used in the WHERE clause?

[aqutalion]

I have a DB that matches image filenames with keywords that describe their contents. It has a table of filename, a table of keywords, and a table that matches the two. It is possible for a given image to have multiple keywords and for a given keywords to be used in multiple images. What I'm trying to do is come up with a query that will return images that have ONLY a specific keyword or keywords (e.g. An image with keyword A, but not one with keyword A and B). One way I thought this could be done is to return, for example, images that are matched with keyword A and only appear once in the match table. The query I came up with (for finding a single keyword) is:

SELECT filename ";
FROM filename_table, match_table, subject_list, COUNT (filename.image_id) AS img_count
WHERE filename_table.image_id = match_table.image_id
AND match_table.subject_id = subject_list.subject_id
AND subject_list.subject = 'keyword'
AND img_count = 1

Needless to say, it doesn't work. Can it work, or is it a lost cause? If it's a lost cause, is there any way to do what I'm looking for that doesn't involve filtering the results in the PHP?

[LifeinAsia]

Try:

SELECT filename, COUNT (filename.image_id) AS img_count
FROM filename_table, match_table, subject_list
WHERE filename_table.image_id = match_table.image_id
AND match_table.subject_id = subject_list.subject_id
AND subject_list.subject = 'keyword'
GROUP BY filename
HAVING COUNT (filename.image_id)=1

[aqutalion]

Thanks for the reply. That code doesn't return any rows, though. If I remove the COUNT line, it returns what I'd expect it to. Why would it behave like this?

[LifeinAsia]

Not sure- that is strange...

But if you're getting the expected results with the modification, I'd go with that.

[aqutalion]

I don't think that's what I meant. I meant that it would return everything with the specified keyword without the modification (whether it had others or not), without the COUNT line, and with the COUNT line, it returned nothing. In other words, it behaved just like the non-working code I posted in the first post without the COUNT. What I was looking for was the specified keywords and no others.

[ZydoSEO]

If I understand it correctly you have a list of images in one table, a list of keyword phrases in another table, and a many-to-many relationship table to relate the images to keywords.

Your query is erroring because to use aggregate functions like COUNT(*), SUM(*), etc. you must use a GROUP BY clause.

That being the case you should be able to do the following in MySQL (I did it in SQL server. COUNT(*) functionality is basically the same in most DBMSs):

CREATE TABLE tImage (
ImageID INT NOT NULL,
ImageFilename VARCHAR (128) NOT NULL
)

CREATE TABLE tKeywordPhrase (
KeywordID INT NOT NULL,
KeywordPhrase VARCHAR (128) NOT NULL
)

CREATE TABLE tImageKeywordPhrase (
ImageID INT NOT NULL,
KeywordID INT NOT NULL
)

INSERT INTO tImage (ImageID, ImageFilename) VALUES (1, 'c:\images\image_apples.jpg')
INSERT INTO tImage (ImageID, ImageFilename) VALUES (2, 'c:\images\image_oranges.jpg')
INSERT INTO tImage (ImageID, ImageFilename) VALUES (3, 'c:\images\image_strawberries.jpg')
INSERT INTO tImage (ImageID, ImageFilename) VALUES (4, 'c:\images\image_bananas.jpg')
INSERT INTO tImage (ImageID, ImageFilename) VALUES (5, 'c:\images\image_pears.jpg')

INSERT INTO tKeywordPhrase (KeywordID, KeywordPhrase) VALUES (1, 'apples')
INSERT INTO tKeywordPhrase (KeywordID, KeywordPhrase) VALUES (2, 'gala apples')
INSERT INTO tKeywordPhrase (KeywordID, KeywordPhrase) VALUES (3, 'red delicious apples')
INSERT INTO tKeywordPhrase (KeywordID, KeywordPhrase) VALUES (4, 'red colored fruit')
INSERT INTO tKeywordPhrase (KeywordID, KeywordPhrase) VALUES (5, 'navel oranges')
INSERT INTO tKeywordPhrase (KeywordID, KeywordPhrase) VALUES (6, 'oranges')
INSERT INTO tKeywordPhrase (KeywordID, KeywordPhrase) VALUES (7, 'orange juice')
INSERT INTO tKeywordPhrase (KeywordID, KeywordPhrase) VALUES (8, 'orange colored fruit')
INSERT INTO tKeywordPhrase (KeywordID, KeywordPhrase) VALUES (9, 'strawberries')
INSERT INTO tKeywordPhrase (KeywordID, KeywordPhrase) VALUES (10, 'bananas')
INSERT INTO tKeywordPhrase (KeywordID, KeywordPhrase) VALUES (11, 'pears')

INSERT INTO tImageKeywordPhrase (ImageID, KeywordID) VALUES (1, 1) -- apple -> apple
INSERT INTO tImageKeywordPhrase (ImageID, KeywordID) VALUES (1, 2) -- apple -> gala apples
INSERT INTO tImageKeywordPhrase (ImageID, KeywordID) VALUES (1, 3) -- apple -> red delicious apples
INSERT INTO tImageKeywordPhrase (ImageID, KeywordID) VALUES (1, 4) -- apple -> red colored fruit
INSERT INTO tImageKeywordPhrase (ImageID, KeywordID) VALUES (2, 5) -- orange -> navel oranges
INSERT INTO tImageKeywordPhrase (ImageID, KeywordID) VALUES (2, 6) -- orange -> oranges
INSERT INTO tImageKeywordPhrase (ImageID, KeywordID) VALUES (2, 7) -- orange -> orange juice
INSERT INTO tImageKeywordPhrase (ImageID, KeywordID) VALUES (2, 8) -- orange -> orange colored fruit
INSERT INTO tImageKeywordPhrase (ImageID, KeywordID) VALUES (3, 9) -- strawberry -> strawberry
INSERT INTO tImageKeywordPhrase (ImageID, KeywordID) VALUES (3, 4) -- strawberry -> red colored fruit
INSERT INTO tImageKeywordPhrase (ImageID, KeywordID) VALUES (4, 10) -- bananas -> bananas
INSERT INTO tImageKeywordPhrase (ImageID, KeywordID) VALUES (5, 11) -- pears -> pears

You can then get the list of images that have 1 and only 1 keyword associated with it using the following query:

SELECT i.ImageID, i.ImageFilename, COUNT(*) AS KeywordCount
FROM tImageKeywordPhrase AS ikp
JOIN tImage AS i ON ikp.ImageID = i.ImageID
GROUP BY i.ImageID, i.ImageFilename
HAVING COUNT(*) = 1

which yields the following results:

ImageID ¦ ImageFilename ¦ KeywordCount
4 ¦ c:\images\image_bananas.jpg ¦ 1
5 ¦ c:\images\image_pears.jpg ¦ 1


To see which images have multiple keywords just change the COUNT(*)=1 to COUNT(*) > 1 as follows:

SELECT i.ImageID, i.ImageFilename, COUNT(*) AS KeywordCount
FROM tImageKeywordPhrase AS ikp
JOIN tImage AS i ON ikp.ImageID = i.ImageID
GROUP BY i.ImageID, i.ImageFilename
HAVING COUNT(*) > 1

which yields the following results:

ImageID ¦ ImageFilename ¦ KeywordCount
1 ¦ c:\images\image_apples.jpg ¦ 4
2 ¦ c:\images\image_oranges.jpg ¦ 4
3 ¦ c:\images\image_strawberries.jpg ¦ 2

To see which keyword is used by a single image use the following:

SELECT kp.KeywordID, kp.KeywordPhrase, COUNT(*) AS ImageCount
FROM tImageKeywordPhrase AS ikp
JOIN tKeywordPhrase AS kp ON ikp.KeywordID = kp.KeywordID
GROUP BY kp.KeywordID, kp.KeywordPhrase
HAVING COUNT(*) = 1

which yields the following results:

KeywordID ¦ KeywordPhrase ¦ ImageCount
1 ¦ apples ¦ 1
2 ¦ gala apples ¦ 1
3 ¦ red delicious apples ¦ 1
5 ¦ navel oranges ¦ 1
6 ¦ oranges ¦ 1
7 ¦ orange juice ¦ 1
8 ¦ orange colored fruit ¦ 1
9 ¦ strawberries ¦ 1
10 ¦ bananas ¦ 1
11 ¦ pears ¦ 1

To see which keyword is used by a multiple images use the following:

SELECT kp.KeywordID, kp.KeywordPhrase, COUNT(*) AS ImageCount
FROM tImageKeywordPhrase AS ikp
JOIN tKeywordPhrase AS kp ON ikp.KeywordID = kp.KeywordID
GROUP BY kp.KeywordID, kp.KeywordPhrase
HAVING COUNT(*) > 1

which yields the following results:

KeywordID ¦ KeywordPhrase ¦ ImageCount
4 ¦ red colored fruit ¦ 2

I'll leave it as an exercise for you to convert this to MySQL syntax (if differences occur between MySQL and Microsoft SQL*Server syntax).

[aqutalion]

Thanks for the detailed reply. It must have taken ages to type all that.

I tried using this:

SELECT i.ImageID, i.ImageFilename, COUNT(*) AS KeywordCount
FROM tImageKeywordPhrase AS ikp
JOIN tImage AS i ON ikp.ImageID = i.ImageID
GROUP BY i.ImageID, i.ImageFilename
HAVING COUNT(*) = 1

and what I need to do is find images that have one -specific- keyword, not just a single keyword. So somewhere, I need to join the subject table and add a WHERE clause to specify the desired keyword.

I tried two versions of this:

SELECT i.image_ID, i.filename, COUNT(*) AS KCount
FROM Images AS i
JOIN Image_Keywords AS s1 ON s1.image_ID = i.image_ID
JOIN Keywords AS list1 ON s1.subject_ID = list1.subject_ID
WHERE list1.subject = 'example'
GROUP BY i.image_ID, i.filename
HAVING COUNT(*) = 1

SELECT i.image_ID, i.filename, COUNT(*) AS KCount
FROM Image_Keywords AS subj
JOIN Images AS i ON subj.image_ID = i.image_ID
WHERE i.filename IN
(SELECT filename
FROM Images AS f
JOIN Image_Keywords as s1 ON f.image_ID = s1.image_ID
JOIN Keywords as list1 ON s1.subject_ID = list1.subject_ID
WHERE list1.subject = 'example')
GROUP BY file.image_ID, file.filename
HAVING COUNT(*) = 1

The first returns every image with "example" as a keyword, whether they have others or not. The second apparently times-out the server.