[MYSQL] Select users who own both a dog and a cat

Question

Hi,

I have this sample table:

CREATE TABLE `dummy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `pet` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;


INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(1, 1, 'dog');
INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(2, 1, 'cat');
INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(3, 2, 'dog');
INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(4, 2, 'cat');
INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(5, 3, 'cat');
INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(6, 4, 'dog');

How can I write the statements below in mysql:

• Retrieve all users who own both a dog and a cat
• Retrieve all users who own a dog or a cat
• Retrieve all users who own only a cat
• Retrieve all users who doesn't own a cat

EDIT: With the help of dbemerlin, I have solutions for first two statements. Here they are:

• Retrieve all users who own both a dog and a cat:

   SELECT * FROM dummy WHERE pet = 'cat' OR pet = 'dog' GROUP BY userId HAVING COUNT(*) = 2
  

• Retrieve all users who own a dog or a cat:

   SELECT * FROM dummy WHERE pet = 'cat' OR pet = 'dog' GROUP BY userId
  

I have found a solution for 3:

• Retrieve all users who own only a cat:

  SELECT * FROM dummy WHERE userId IN (SELECT userId FROM dummy WHERE pet = 'cat' GROUP BY userId) GROUP BY userId HAVING COUNT(*) = 1
  

  But Adriano have a better solution:

  SELECT * FROM dummy WHERE pet = 'cat' AND userId NOT IN (SELECT userId FROM dummy WHERE pet != 'cat');
  

But still having problems for the last statement:

• Retrieve all users who doesn't own a cat:

   SELECT * FROM dummy WHERE pet != 'cat' GROUP BY userId
  

  This doesn't work either. What I exactly need is that to retrieve all user who doesn't own a cat but may have other pets.

Thanks!

EDIT: This is not a homework assignment. I tried to simplify the question for asking here and also to isolate the problem. The real situation is I am trying to retrieve users who have click 2 different links (stored as url strings) and etc. And if this was a homework assignment, what's the wrong in asking how to achieve this here? If I had a friend of mine who had MySQL knowledge, what's the difference asking him to tell me the solution and explain than asking here?

Answer 1

Sounds like a homework assignment..what do you have so far? We can help from there.

Answer 2

1:

SELECT u.user_id
FROM   user u
INNER JOIN dummy d
USING (user_id)
WHERE d.pet = 'cat'
OR d.pet = 'dog'
GROUP BY user_id
HAVING COUNT(*) = 2

2:

SELECT u.user_id
FROM   user u
INNER JOIN dummy d
USING (user_id)
WHERE d.pet = 'cat'
OR d.pet = 'dog'
GROUP BY user_id

3:

SELECT u.user_id
FROM   user u
INNER JOIN dummy d
USING (user_id)
WHERE d.pet = 'cat'

4:

SELECT u.user_id
FROM   user u
INNER JOIN dummy d
USING (user_id)
WHERE d.pet = 'dog'

Answer 3

One solution to your latter problems could be this:

SELECT * 
FROM dummy 
WHERE pet = 'cat' 
AND userId NOT IN (
    SELECT userId 
    FROM dummy 
    WHERE pet != 'cat'
);

for users who only have cats.

This lets you use a single variable to represent the type of pet you want selected.

The result here, with the data you posted:

mysql> select * from dummy where pet = 'cat' and userId not in \
    -> (select userId from dummy where pet != 'cat');
+----+--------+-----+
| id | userId | pet |
+----+--------+-----+
|  5 |      3 | cat |
+----+--------+-----+
1 row in set (0.00 sec)

EDIT: For your last problem, you just reverse the = and != in the selects. Do try to think about it for a second before asking.

EDIT: You want to know about performance. One tool offered by MySQL is EXPLAIN. Prefixing your query with the keyword EXPLAIN will give you an analysis of its performance, possible pathway of execution, keys and indexes involved, etc. In this case:

mysql> explain select * from dummy where pet = 'cat' and userId not in (select userId from dummy where pet != 'cat');
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | dummy | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
|  2 | DEPENDENT SUBQUERY | dummy | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

mysql> explain SELECT * FROM dummy WHERE userId IN (SELECT userId FROM dummy WHERE pet = 'cat' GROUP BY userId) GROUP BY userId HAVING COUNT(*) = 1;
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | PRIMARY            | dummy | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | dummy | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where; Using temporary; Using filesort |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
2 rows in set (0.00 sec)

You'll notice that your query adds a "using temporary, using filesort" to the 'extra' column. That, in brief, means it is less efficient, because a temporary table must be created, and sorting must occur for your result to be calculated. You can read this manpage to know more.

Answer 4

There are several solutions to the first problem, "users that have at least one dog and one cat."

  select * 
    from dummy
    where userId in (select userId from dummy where pet = 'dog')
     and  userId in (select userId from dummy where pet = 'cat');

Using a correlated sub query:

    select * 
    from dummy
    where exists (select 1 from dummy p where p.userId = d.userId and pet = 'dog')
     and  exists (select 1 from dummy p where p.userId = d.userId and pet = 'cat')

Performance depends on the optimizer. It's possible both have the same execution plan.

  select d.* 
    from dummy d
    join (select distinct userId, pet from dummy where pet = 'dog') as g
    using (userId)
    join (select distinct userId, pet from dummy where pet = 'cat') as c
    using (userId);

The last one uses what Oracle calls an "inline view." Other database vendors have a different name. If these queries don't work in mySql, let me know.