mysql AND morethan one time for a column

Question

SELECT DISTINCT group_id FROM mark_mapping 
WHERE subcat_id = 22 AND subcat_id = 30 AND subcat_id = 17 AND subcat_id = 18 AND subcat_id = 19

This is my sample query. As you know.. this is not working. Help me please.

Here subcat_id may can repeat more than 10 times. All are same columns.

I would like to metion my table structure here....

group_id subcat_id

12 -------> 7
12 -------> 8
12 -------> 9
12 -------> 10
13 -------> 7
13 -------> 11
14 -------> 8
14 -------> 9

so on.. So what is the correct to get this result...

SELECT DISTINCT group_id FROM mark_mapping WHERE subcat_id = 22 AND subcat_id = 30 AND subcat_id = 17 AND subcat_id = 18 AND subcat_id = 19

SELECT group_id FROM mark_mapping WHERE subcat_id in (22,30,17,18,19) group by group_id having count(distinct subcat_id) = 5

I know that the AND statement is looking for different column. Can any one tell me how can I use it for a column...

this will newer give you anything. How can subcat_id be 22 AND same time 30? — Hardy, Dec 11 '13 at 11:42
IN is taking all the group_id's.. its like OR operator..I need AND operator for a column — Anish Charles, Dec 11 '13 at 11:44
@Hardy `OR` or `IN` is the solution. You cannot have a row which constains one column twice or more — Royal Bg, Dec 11 '13 at 11:45
@RoyalBg Really? what about UNION. I had this one project where was used "OR" and changing code to use UNION made SQL 10000 faster. — Hardy, Dec 11 '13 at 11:47
Thank you all.. for the response.. still I am working on it.. — Anish Charles, Dec 11 '13 at 12:15

juergen d · Accepted Answer · 2013-12-11T12:19:16.483

3

SELECT group_id 
FROM mark_mapping 
WHERE subcat_id in (22,30,17,18,19)
group by group_id 
having count(distinct subcat_id) = 5

SQLFiddle demo

edited Dec 11 '13 at 12:19

answered Dec 11 '13 at 11:42

juergen d

201,996
37
293
362

I thought of same but he written AND in between – Suresh Kamrushi Dec 11 '13 at 11:42
Never! use OR.. it is really really really slow. Trust me i know! Use UNION instead – Hardy Dec 11 '13 at 11:44
@Hardy are you serious? – Royal Bg Dec 11 '13 at 11:45
KIndly note I am looking AND operation.. Is it possible with the table structure... – Anish Charles Dec 11 '13 at 11:47
I have benchmarked that! Try by your self UNION is lot faster than OR. At least in MySql databases. – Hardy Dec 11 '13 at 11:49
@AnishCharles, nt, it's not possible. If you have a row which contains `bla_id = 20` and `foo_id = 30` and you tell it: `SELECT theRow FROM tbl WHERE bla_id = 20 AND foo_id = 30` it will work, because for a single row both match. But they cannot match, if you give it `WHERE bla_id = 20 AND bla_id = 30` because bla_id cannot be in one row more than one value. You will need more columns. It will not be normalized table structure, but you will need subcat_id1, subcat_id2, etc... – Royal Bg Dec 11 '13 at 11:49
@juergend All i have to say: http://stackoverflow.com/questions/5639710/union-all-vs-or-condition-in-sql-server-query – Hardy Dec 11 '13 at 11:56
@Hardy: Totally not comparable to this question. But you are welcome to set up a SQLFiddle and output the execution plans to prove me wrong. – juergen d Dec 11 '13 at 12:02
@Royal BG -> I can't do that.. because subcat_id is a variable.. it can be many.... – Anish Charles Dec 11 '13 at 12:06
@AnishCharles I think juergen's solution for grouping will be ok? – Royal Bg Dec 11 '13 at 12:09
Guys... I know that the AND statement is looking for different column. Can any one tell me how can I use it for a column... – Anish Charles Dec 11 '13 at 12:10
juergen d:-> I hope the solution is correct.. but little slow.. I can make it working.. thanks ... – Anish Charles Dec 11 '13 at 12:25

Royal Bg · Answer 2 · 2013-12-11T11:56:29.037

1

The query is against a row. You cannot have one row, where column has 2 or more different values. You need OR instead of AND or better IN(...):

SELECT DISTINCT group_id FROM mark_mapping 
WHERE subcat_id IN (22, 30, 17, 18, 19);

edited Dec 11 '13 at 11:56

answered Dec 11 '13 at 11:42

Royal Bg

6,988
1
18
24

score 0 · Answer 3 · answered Dec 11 '13 at 11:48

You are using the wrong operator.

If you combine conditions using AND, rows will only be matched if they meet all requirements. In this case, that's impossible. Each row can only have a single subcat_id, so there can't be a row who's subcat_id is 22, 30, 17, 18 AND 19 at the same time.

You should use the OR operator instead:

WHERE subcat_id = 22 OR subcat_id = 30 OR subcat_id = 17 OR subcat_id = 18 OR subcat_id = 19

Which, translated to plain English, says:

Give me all rows where the subcat_id is either 22, 30, 17, 18 OR 19.

In this case you can also simplify your query by using IN:

SELECT DISTINCT group_id
FROM mark_mapping 
WHERE subcat_id IN (22, 30, 17, 18, 19)

This basically looks for rows where subcat_id exists IN the provided list of values.

mysql AND morethan one time for a column

3 Answers3

SQLFiddle demo