Reputation: 14845
I have the following simple query that works just fine when there is one keyword to match:
SELECT gc.id, gc.name
FROM gift_card AS gc
JOIN keyword ON gc.id = keyword.gc_id
WHERE keyword = 'mini'
GROUP BY gc.id
ORDER BY id DESC
What I want to do is find the id
's that match at least two of the keywords I provide. I thought just adding a simple AND
would work but I get blank results.
SELECT gc.id, gc.name
FROM gift_card AS gc
JOIN keyword ON gc.id = keyword.gc_id
WHERE keyword = 'mini'
AND keyword = '2012'
GROUP BY gc.id
ORDER BY id DESC
Obviously SQL is not my strong suit so I am looking for some help one what I am doing wrong here.
Here are my table structures:
CREATE TABLE `gift_card` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8;
CREATE TABLE `keyword` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`gc_id` int(11) NOT NULL,
`keyword` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`),
UNIQUE KEY `dupes_UNIQUE` (`gc_id`,`keyword`)
) ENGINE=InnoDB AUTO_INCREMENT=477 DEFAULT CHARSET=utf8;
Upvotes: 0
Views: 66
Reputation: 1271231
No, and
does not work. A column cannot have two different values in one row.
Instead, or
. . . and a bit more logic:
SELECT gc.id, gc.name
FROM gift_card gc JOIN
keyword k
ON gc.id = k.gc_id
WHERE k.keyword IN ('mini', '2012')
GROUP BY gc.id
HAVING COUNT(*) = 2 -- both match
ORDER BY id DESC;
It is a good idea to qualify all column names in a query that has more than one table reference.
Upvotes: 1