CODEWITHSUNDEEP
mysql
oka96
oka96

Reputation: 423

MySQL condition statement across multiple rows

I have a Profile table like this

|--------|-----------|
| People | Favorite  |
|--------|-----------|
|   A    |   Movie   |
|   B    |   Movie   |
|   B    |  Jogging  |
|--------|-----------|

Q: How to retrieve the people whose favorite is movie but not jogging?

In this table, the result is only People A.

Although I came out with this

select People from Profile
where
People
in
(select People from Profile
where favorite='Movie')
and
People
not in
(select People from Profile
where favorite='Jogging')

But it seem like can be better, any suggestion or answer (without using join or union clause)?

Upvotes: 3

Views: 1541

Answers (5)

forpas
forpas

Reputation: 164069

With group by people and checking the minimum and maximum values of favorite to be 'Movie':

select people from tablename
where favorite in ('Movie', 'Jogging')
group by people
having min(favorite) = 'Movie' and max(favorite) = 'Movie'

Upvotes: 0

Rahul Kapoor
Rahul Kapoor

Reputation: 62

This is a common problem when you want to have multiple conditions with the same column. I have answered this here and there are other methods like intersect and subqueries.

SELECT people, GROUP_CONCAT(favorite) as fav
FROM profile
GROUP BY people
HAVING fav REGEXP 'Movie'
AND NOT fav REGEXP 'Jogging';

Upvotes: 0

Alex
Alex

Reputation: 17289

https://www.db-fiddle.com/f/rboiDpxxbABCpjtduEz7uY/1

SELECT People 
FROM `profile`
GROUP BY people
HAVING SUM('Movie' = favorite) > 0
   AND SUM('Jogging' = favorite) = 0

Upvotes: 3

spencer7593
spencer7593

Reputation: 108370

There are several query patterns that will return a result that satisfies the specification.

We can use NOT EXISTS with a correlated subquery:

SELECT p.people 
  FROM profile p 
 WHERE p.favorite = 'Movie' 
   AND NOT EXISTS ( SELECT 1
                      FROM profile q
                     WHERE q.favorite = 'Jogging' 
                       AND q.people   = p.people   /* related to row in out query */
                  )
 ORDER
    BY p.people

An equivalent result can also be done with an anti-join pattern:

SELECT p.people 
  FROM profile p
  LEFT
  JOIN profile q
    ON q.people   = p.people
   AND q.favorite = 'Jogging'
 WHERE q.people IS NULL
   AND p.favorite = 'Movie'
 ORDER BY p.people

Another option is conditional aggregation. Without a guarantee about uniqueness, and some MySQL shorthand:

SELECT p.people
  FROM profile p
 GROUP
    BY p.people
HAVING 1 = MAX(p.favorite='Movie')
   AND 0 = MAX(p.favorite='Jogging')

A more portable more ANSI standard compliant syntax for the conditional aggregation:

SELECT p.people
  FROM profile p
 GROUP
    BY p.people
HAVING 1 = MAX(CASE p.favorite WHEN 'Movie'  THEN 1 ELSE 0 END)
   AND 0 = MAX(CASE p.favorite WHEN Jogging' THEN 1 ELSE 0 END)

Upvotes: 0

symcbean
symcbean

Reputation: 48357

There's lots of ways. While you can use a UNION, its rather messy and innefficient. MySQL doesn't have a MINUS clause which would give a fairly easy to understand query.

You could aggregate the data:

SELECT people
, MAX(IF(favorite='jogging', 1, 0)) as jogging
, MAX(IF(favorite='movie', 1, 0)) as movie
FROM profile
GROUP BY people
HAVING movie=1 AND jogging=0

Or use an outer join:

SELECT m.people
FROM profile m
LEFT JOIN
 ( SELECT j.people
 FROM joggers j
 WHERE j.favorite='jogging' ) joggers
ON m.people=joggers.people
WHERE joggers.people IS NULL
AND m.favorite='movies'

Using a NOT IN/NOT EXISTS gives clearer syntax but again would be very innefficient.

Upvotes: 1

Related Questions