0

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;
  • A PRIMARY KEY is a UNIQUE KEY, so the latter is redundant. – Rick James May 9 '18 at 22:42
2

Consider that without the group-by or the where clause, your dataset might look like this:

(row) gc.id  gc.name  keyword.keyword
1     1      Fred     mini
2     1      Fred     tall
3     1      Fred     2011
4     2      Bob      mini
5     2      Bob      2012
6     3      Pat      2012
7     4      Kim      2011
8     4      Kim      mini
9     4      Kim      2012

The first thing that happens in your query is that the where clause evaluates each row individually to see if it matches all of the conditions. Only after this step does it apply the grouping. (Technically there are a few more steps involved; see Logical Processing Order of the SELECT Statement for more gory details.)
With that in mind, though, your initial query first finds every row where the keyword is "mini", which in the sample dataset above is rows 1, 4 and 8 (card IDs 1, 2, and 4), then groups the found rows by their card ID. Your modified query is asking for every individual row where the keyword is both "mini" and "2012", in the same row. There are rows with "mini" and there are rows with "2012", but each row has only one value or the other, not both at the same time. In fact it is simply not possible for a single field to hold two different values at the same time.

What you need to do is to select all rows where the keyword is any one of your values, then group them by ID, and then select the IDs where the number of rows found for that ID is 2:

Select gc.id, min(gc.name) 
from giftcard as GC 
join keyword as KW on gc.id = kw.gc_id
where kw.keyword in ('mini','2012')
group by gc.id 
having count(gc.id) = 2
order by gc.id desc

From our dataset above, this will basically go through and select rows 1, 4, 5, 6, 8, and 9; reduce that to "card 1: 1 match, card 2: 2 matches, card 3: 1 match, card 4: 2 matches", and then select "card 2, card 4" as having the required 2 matches, and produce the output

gc.id  gc.name
4      Kim
2      Bob

You could then extend this to say "any card having at least X out of these Y keywords" by adding keywords to the in clause (so it would be like in ('x','b','z','r','p')), and adjusting the having clause to something like having count(gc.id) >= 3.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.