How can I perform an AND operation on a single column?

Question

I have a table with two columns;

CREATE TABLE IF NOT EXISTS `QUESTION_CATEGORY_RELATION` (
  `question_id` int(16) NOT NULL,
  `category_id` int(16) NOT NULL,
  KEY `category_id` (`category_id`),
  KEY `question_id` (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `QUESTION_CATEGORY_RELATION` (`question_id`, `category_id`) VALUES
(1, 2),
(1, 3),
(2, 1),
(2, 2);

enter image description here

I want to create a query that will search for a question_id with content_id 2 and content_id 3

e.g.:

SELECT *  
FROM `QUESTION_CONTENTS_REL` 
WHERE `content_id` = 2 AND `content_id` = 3

But do you have rows that contain content_id 2 and 3? And I'm not sure what the ' is for? — CustomX, Oct 25 '12 at 11:44
yes but this query will look for single entry with values 2 and 3 and hence it will return 0 rows. — Anu Sad, Oct 25 '12 at 11:46
What you want is called relational division. Here's a question with quite a lot of ways to do it: **[How to filter SQL results in a has-many-through relation](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation)** — ypercubeᵀᴹ, Oct 25 '12 at 12:33

juergen d · Answer 1 · 2012-10-25T20:46:46.250

3

SELECT question_id
FROM QUESTION_CONTENTS_REL
WHERE content_id in (2, 3)
group by question_id
having count(distinct content_id) = 2

edited Oct 25 '12 at 20:46

answered Oct 25 '12 at 11:24

juergen d

201,996
37
293
362

hi juergen d I want to perform AND operation but IN clause will give me answer of 2 OR 3 OR BOTH, with above data, i want only question_id 1 in answer because it is the only entry having both the content ids. – Anu Sad Oct 25 '12 at 11:31
2

Add a `)` in the `having` clause and this is what the op is asking for. *[@AnuSad : The `IN` clause does mean `2 OR 3`, but then the `COUNT()` in the HAVING clauses means `and has both`. I suggest that you **try** the code before stating that it is incorrect.]* – MatBailie Oct 25 '12 at 11:32
@juergen: OP didn't want this... He wanted those `question_ids` that have **both** `content_ids`, not **either** – ppeterka Oct 25 '12 at 12:02
1

Anu Sad and ppeterka -- please read Dems' explanation before dismissing the answer. The only way for a returned row to have two distinct content_ids are for it to be associated both with 2 and 3, so only question_id=1 qualifies – michel-slm Oct 25 '12 at 15:52

score 1 · Answer 2 · edited Oct 25 '12 at 11:32

1

I might be missinterpreting but i think you are looking for OR instead since the content_id is a key

SELECT * FROM QUESTION_CONTENTS_REL WHERE `content_id`= 2 OR `content_id` = 3

edited Oct 25 '12 at 11:32

nawfal

70,104
56
326
368

answered Oct 25 '12 at 11:27

macken88

125
11

i am looking for the answer which will give me only question id 1 in output because it has content_id=2 AND content_id=3 – Anu Sad Oct 25 '12 at 11:41
this will use OR and not AND ... you will have content_id 2, content_id 3 and content_id 2+3 – CustomX Oct 25 '12 at 11:43
OP didn't want this... He wanted those `question_ids` that have **both** `content_ids`, not **either** – ppeterka Oct 25 '12 at 12:04

ppeterka · Answer 3 · 2012-10-25T12:01:20.363

1

You can try this:

SELECT q1.question_id 
FROM `QUESTION_CONTENTS_REL` q1
JOIN `QUESTION_CONTENTS_REL` q2 on q1.question_id=q2.question_id
WHERE q1.`content_id` = 2 
AND q2.`content_id` = 3

This is not the nicest solution available, as it has a JOIN, for example in Oracle, I'd do this:

SELECT q.question_id 
FROM `QUESTION_CONTENTS_REL` q
GROUP BY q.`question_ID`
HAVING SUM(case when q.`content_id` = '2' then 1 else 0 end)>0 
AND SUM(case when q.`content_id` = '2' then 1 else 0 end)>0

edited Oct 25 '12 at 12:01

answered Oct 25 '12 at 11:52

ppeterka

20,583
6
63
78

1

Yeah, thanks it is working, but i would like to ask, there is many-to-many realtion in question and contents hence created this third table. and requirement is I want questions having contentIDs=2 as well as 3. so, according to requirement, is my design correct ? your query worked. – Anu Sad Oct 25 '12 at 11:57
@AnuSad How do you want to handle if the number of `content_id`s to be compared are increased? – Ravinder Reddy Oct 25 '12 at 12:07

How can I perform an AND operation on a single column?

3 Answers3