MySQL multi-row SELECT query on SINGLE column

In my mysql database I have two columns setup like this (for example):

series_id   category_id
    1            1
    1            2
    1            3
    2            1
    2            3
    3            2
    4            1
    4            2

What is the proper select query I can use to select the series_id when the category_id = 1 and 2 ?

Current query is this:

SELECT series_id 
FROM table 
WHERE category_id = '1' 
OR category_id = '2' 
GROUP BY series_id"

because this:

SELECT series_id 
FROM table 
WHERE category_id = '1' 
AND category_id = '2' 
GROUP BY series_id

does not retrieve any results

In regards to this example I would like it to return the two rows where the series_id is 1 and 2 so the result looks like this:

series_id
     1
     4

(Optional / Bonus) If it is possible to come up with another solution or utilize:

GROUP_CONCAT(category_id SEPARATOR ', ') AS category

for the returned series_id (s) from this query so the returned result is:

series_id    category
     1       1, 2, 3
     4       1, 2

That would be very much appreciated. Thank you for taking time out of your day to help me with this challenging problem.

Answers


SELECT   series_id
FROM     my_table
WHERE    category_id IN (1,2)
GROUP BY series_id
HAVING   COUNT(*) = 2

See it on sqlfiddle.

Note that if (series_id, category_id) are not guaranteed to be unique, you should replace COUNT(*) with the less performant COUNT(DISTINCT category_id).

To return the grouped results, you can join against your table again:

SELECT series_id, GROUP_CONCAT(category_id)
FROM   my_table NATURAL JOIN (
  SELECT   series_id
  FROM     my_table
  WHERE    category_id IN (1,2)
  GROUP BY series_id
  HAVING   COUNT(*) = 2
) t
GROUP BY series_id

See it on sqlfiddle.


Need Your Help

Creating an atoi Function

c function atoi

I'm attempting to create my own atoi function. With the following I'm getting a return value of 0. Whatever I change the number variable within the function is what I get as a return value. Any

NullReferenceException when assigning dataset as datasource of dropdownlist

asp.net vb.net

I want to assign datasource of a dropdownlist which is inside a gridview control. But when i am executing the following code i am getting NullReferenceException.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.