Mysql query select in the same table

Question

I have table people

Name               |       Score    |   Date

Don                |         A      |   12-10-2014              
Don                |         B      |   12-10-2014           
Mary               |         A      |   12-10-2014
Mary               |         C      |   12-10-2014
Mary               |         D      |   12-10-2014
Jack               |         A      |   12-10-2014
Jack               |         B      |   12-10-2014
Jack               |         C      |   12-10-2014

I want to get records which people with score A and B.

Mary | A | 12-10-2014

will no be in the result because she doesn't have score B. The result must both have A and B.

So the output should be the following. Mary should not in the result because she only has score A

Name               |       Score    |   Date

Don                |         A      |   12-10-2014              
Don                |         B      |   12-10-2014          
Jack               |         A      |   12-10-2014
Jack               |         B      |   12-10-2014

I have tried the following query but the result is not right.

 select * 
 from people 
 where score='B' and Name in (select Name 
                              from people 
                              where score='A' 
                              group by Name)

WHERE score IN ('a','b') GROUP BY name HAVING COUNT(DISTINCT score) = 2; — Strawberry
– Strawberry, Commented Dec 23, 2014 at 23:16
Well, it is right. If you want their complete results, just join the source table back on to that. — Strawberry
– Strawberry, Commented Dec 23, 2014 at 23:27

O. Jones · Accepted Answer · 2014-12-23 23:21:27Z

1

This subquery gets you the list of Name values with at least one Score of A and one of B.

SELECT DISTINCT a.Name 
  FROM people AS a
  JOIN people AS b ON a.Name = b.Name AND a.score='A' AND b.score = 'B'

Then, you can join to the subquery to pull the records you need.

SELECT people.Name, people.Score, people.Date
  FROM people
  JOIN (
        SELECT DISTINCT a.Name 
          FROM people AS a
          JOIN people AS b ON a.Name = b.Name AND a.score='A' AND b.score = 'B'
       ) ab ON ab.Name = people.Name
 WHERE Score IN ('A','B')

answered Dec 23, 2014 at 23:21

O. Jones

109k17 gold badges134 silver badges187 bronze badges

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

Comments

Strawberry · Accepted Answer · 2014-12-23 23:15:11Z

1

You can do a self-join, something like this:

SELECT * FROM people p1 JOIN people p2 ON p1.name = p2.name WHERE p1.score = 'A' and p2.score = 'B'

edited Dec 23, 2014 at 23:15

Strawberry

34k14 gold badges43 silver badges57 bronze badges

answered Dec 23, 2014 at 23:14

Maltronic

1,81211 silver badges21 bronze badges

2 Comments

Tester Over a year ago

the result is Don A 1 Don B 2 Jack A 2 Jack B 2 seems not right. How to get 4 records instead of 2?

Maltronic Over a year ago

To avoid returning unwanted columns, replace the * with the individual field names you need, e.g. p1.name, p1.score, p1.date. To get the additional records, you could query for the score combination both ways round, e.g. WHERE (p1.score = 'A' AND p2.score = 'B') OR (p1.score = 'B' and p2.score = 'A')

Gordon Linoff · Accepted Answer · 2014-12-24 00:30:22Z

1

I think two exists clauses are perhaps the most logical approach:

select p.*
from people p
where score in ('A', 'B') and
      exists (select 1 from people p2 where p2.name = p.name and p2.score = 'A') and
      exists (select 1 from people p2 where p2.name = p.name and p2.score = 'B');

answered Dec 24, 2014 at 0:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Ataboy Josef · Accepted Answer · 2014-12-24 03:19:37Z

0

Using Alias names will reduce the complexity and it seems much easy than using table names. Here is the updated one with join to the subquery to pull the records needed as an extended and updated answer from @Olie's answer:

 SELECT P.Name AS Name, P.Score AS Score, P.Date AS Date
  FROM people P
  JOIN (
        SELECT DISTINCT P1.Name 
          FROM people P1
          JOIN people P2 ON P1.Name = P2.Name AND P1.score='A' AND P2.score = 'B'
       ) P3 ON P3.Name = P.Name
 WHERE P.Score IN ('A','B')

answered Dec 24, 2014 at 3:19

Ataboy Josef

2,1113 gold badges24 silver badges28 bronze badges

Collectives™ on Stack Overflow

Mysql query select in the same table

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related