A mysql query: couple of conditions matching in the same column

Question

I have this table named "events" in my mysql database:

+-----+-----------+------------------------------+------------+
| ID  | CATEGORY  | NAME                         | TYPE       |
+-----+-----------+------------------------------+------------+
| 1   | 1         | Concert                      | music      |
| 2   | 2         | Basketball match             | indoors    |
| 3   | 1         | Theather play                | outdoors   |
| 4   | 1         | Concert                      | outdoors   |
+-----+-----------+------------------------------+------------+

I need a query to count the events with category 1 and which type is music and also outdoors Meaning that from the table above the count should be only 1: there are three events with category 1 but only "Concert" has type outdoor and music (ID 1 and ID 4).

What would be that query? Can that be done?

@Mat Are you sure you payed enough attention to the question? It doesn't look basic to me. — Alin Purcaru, Aug 30 '11 at 11:49
i have one that works for what i think is what you want but for some reason i can't post it... — rownage, Aug 30 '11 at 11:50
SELECT ID, COUNT(CATEGORY) FROM events WHERE CATEGORY=1 AND TYPE='music' AND TYPE='outdoors' GROUP BY ID — user712027, Aug 30 '11 at 11:54

Alin Purcaru · Accepted Answer · 2011-08-30T12:24:44.553

3

Try this:

SELECT count(DISTINCT e1.name)
FROM `events` AS e1
JOIN `events` AS e2 ON e1.name = e2.name
WHERE e1.category = 1 
    AND e2.category = 1 
    AND e1.type = 'music' 
    AND e2.type = 'outdoor'

Or a harder to understand way, but way faster than the previous one:

SELECT count(*) FROM (
    SELECT `name`
    FROM `events`
    WHERE `category` = 1
    GROUP BY `name`
    HAVING SUM( `type` = 'music') * SUM( `type` = 'outdoor' ) >= 1
) AS notNeeded

edited Aug 30 '11 at 12:24

answered Aug 30 '11 at 11:48

Alin Purcaru

43,655
12
77
90

The second query is not the same as the first. The first is correct, the second one not. – Johan Aug 30 '11 at 11:58
I don't agree that this indicates a bad structure. [Relational Division](http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/) often arises in normalised databases. How would you restructure it? – Martin Smith Aug 30 '11 at 12:02
@Johan Apart from a missing alias what isn't correct about it? Also I added the alias. – Alin Purcaru Aug 30 '11 at 12:03
@Alin, the second query will also yield rows that have 3x `music` types and no `outdoor` – Johan Aug 30 '11 at 12:12
@Johan You were right. I assumed the `music` and `type` combination unique. I corrected it. Also added a correction for the first one based on the same observation. Thank you! – Alin Purcaru Aug 30 '11 at 12:19
I agree it looks as though `category` is functionally dependant upon `name` and there should be some separation out but at some point you are still going to have a junction table with `eventid, type` that needs this kind of query. – Martin Smith Aug 30 '11 at 12:19
@Alin, wow a thing of beauty, query 2 will still break if `type` is allowed to be NULL, but you got my vote. – Johan Aug 30 '11 at 12:25
@Martin I removed my comment about normalization. After some thinking I must admit that there actually may be a situation in which this structure is in normal form. So there's no point in assuming conditions he didn't provide. – Alin Purcaru Aug 30 '11 at 12:26
@Johan Why? Could you explain a bit? – Alin Purcaru Aug 30 '11 at 12:29

score 2 · Answer 2 · answered Aug 30 '11 at 11:54

2

For 2 criteria I would use Alin's answer. An approach you can use for greater numbers is below.

SELECT COUNT(*)
FROM   (SELECT `name`
        FROM   `events`
        WHERE  `category` = 1
               AND `type` IN ( 'outdoors', 'music' )
        GROUP  BY `name`
        HAVING COUNT(DISTINCT `type`) = 2) t

answered Aug 30 '11 at 11:54

Martin Smith

438,706
87
741
845

Yeah... I wrote the first approach because it's easier to understand, but it's a terrible choice even for 2 values. – Alin Purcaru Aug 30 '11 at 11:58

score 1 · Answer 3 · answered Aug 30 '11 at 11:57

1

Try this query

Select count(*), group_concat(TYPE SEPARATOR ',') as types 
from events where category = 1 
HAVING LOCATE('music', types) and  LOCATE('outdoors', types)

answered Aug 30 '11 at 11:57

Andrej

7,474
1
19
21

galchen · Answer 4 · 2011-08-30T11:54:00.457

-1

try:

SELECT * FROM `events` AS e1
LEFT JOIN `events` AS e2 USING (`name`)
WHERE e1.`category` = 1 AND e2.`category` = 1 AND e1.`type` = 'music' AND e2.`type` = 'outdoors'

edited Aug 30 '11 at 11:54

answered Aug 30 '11 at 11:47

galchen

5,252
3
29
43

score -1 · Answer 5 · answered Aug 30 '11 at 11:56

-1

SELECT COUNT(*)
    FROM table
    WHERE category=1
    AND type='music' AND type IN (SELECT type
                                  FROM table
                                  WHERE type = 'outdoor')

one line keeps resetting my connection. wth? i'll try posting as a comment

answered Aug 30 '11 at 11:56

rownage

2,392
3
22
31

anybody know why i couldn't type that without my connection being refused? – rownage Aug 30 '11 at 12:58

score -2 · Answer 6 · answered Aug 30 '11 at 11:48

-2

Select count(distinct ID) as 'eventcount' from events where Category = '1' and Type in('music','outdoor')

answered Aug 30 '11 at 11:48

Gunarathinam

436
1
5
14

count(*) is faster, anyway you need a self-join, see Alin's answer. – Johan Aug 30 '11 at 11:49
@Martin, http://stackoverflow.com/questions/2710621/count-vs-count1-vs-countpk-which-is-better and countless and countless links on just about every SQL blog – Johan Aug 30 '11 at 11:52
@Martin, but I guess I should have written `count(*) is never slower, sometimes the same and sometimes faster` – Johan Aug 30 '11 at 11:53
@Johan - Agreed `id` sounds like the PK so definitely in SQL Server it makes no odds. – Martin Smith Aug 30 '11 at 11:56

A mysql query: couple of conditions matching in the same column

6 Answers6