CODEWITHSUNDEEP
mysqlsqljoin
Ethan Allen
Ethan Allen

Reputation: 14845

How do I SELECT from a table with a JOIN with multiple matching values?

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions