MySQL: Selecting data based on values from multiple rows

Question

I am trying to write a SQL statement that selects data out of a table where an individual has multiple values in the same column. For example, based on the table below: I need a query to select all of the rows for all of the individuals who have both a bat and a baseball in the “Toy” column.

------------------------
| ID | Name | Toy      |
------------------------
|  1 | Jack | Bat      |
|  2 | Jim  | Baseball |
|  3 | Jack | Baseball |
|  4 | John | Bat      |
|  5 | Jim  | Football |
|  6 | Jack | Glove    |
------------------------

I would like the results to be something like:

-------------------
| Name | Toy      |
-------------------
| Jack | Bat      |
| Jack | Baseball |
-------------------

I hope this makes sense. Thanks.

You're going to need to clarify this. I thought you wanted a query that selects ALL names that have more than one toy. Juergen, and others, apparently, think you want one where there is a specific combination of two toys. — outis nihil
– outis nihil, Commented Nov 12, 2013 at 17:48

juergen d · Accepted Answer · 2013-11-12 16:23:47Z

5

select distinct t.name, t.toy
from your_table t
where name in 
(
  select name
  from your_table
  where toy in ('bat','baseball') 
  group by name
  having count(distinct toy) = 2
)
and toy in ('bat','baseball')

If you just need the name you can do

  select name
  from your_table
  where toy in ('bat','baseball') 
  group by name
  having count(distinct toy) = 2

SQLFiddle demo

edited Nov 12, 2013 at 16:23

answered Nov 12, 2013 at 15:52

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

juergen d Over a year ago

No, I want =2 because it is exactly 2. Always, because of the where condition.

Collectives™ on Stack Overflow

MySQL: Selecting data based on values from multiple rows

1 Answer 1

SQLFiddle demo

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related