MYSQL compare values from same columns

Question

Okay I tried to look all over stackoverflow, and the closest solution I found is this: mysql AND clause on same column multiple times

But I can't use statements and "having" syntax won't work because of group by. There MUST be a simple solution to this.

The 2 tables looks like this (simplified):

users:

uid  name
1    person 1
2    person 2
3    person 3

categories:

uid  value
1    actor
1    musician
2    actor
3    dancer
4    musician
4    dancer

I want to get the uid of those that are 2 values at the same time. For example, I want to get the UID that is an actor AND a musician. Not just one value, but both of them must be required!

First I tried this:

SELECT users.uid, users.name 
FROM 
users
LEFT OUTER JOIN categories ON users.uid = categories.uid
WHERE (categories.value = 'actor' AND categories.value = 'musician')
GROUP BY u.uid;

This of course does not work since one row can't have 2 values.

Does anyone know a solution?

Taryn · Accepted Answer · 2013-05-22 11:47:09Z

4

You can JOIN to the categories table multiple times to get the result:

SELECT users.uid, users.name 
FROM users
INNER JOIN categories c1
  ON users.uid = c1.uid
INNER JOIN categories c2
  ON users.uid = c2.uid
WHERE c1.value = 'actor' 
  AND c2.value = 'musician';

See SQL Fiddle with Demo

answered May 22, 2013 at 11:47

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

1 Comment

Raf A. Over a year ago

This one made it work for me. My db is more complicated than so, but I simplified the problem, and I have to "translate" it to Drupal's DB API, perhaps that's why using 'having' did not work, or perhaps because I joined 5 other tables... I don't know, my first thought was that "group by" only results 1 row, so count would always be 1, but I don't know...

user330315 · Accepted Answer · 2013-05-22 11:45:30Z

4

SELECT users.uid, users.name 
FROM users
  LEFT JOIN categories ON users.uid = categories.uid
WHERE categories.value in ('actor', 'musician')
GROUP BY u.uid, users.name
having count(distinct categories.value) = 2;

answered May 22, 2013 at 11:45

user330315

Comments

juergen d · Accepted Answer · 2013-05-22 11:45:24Z

3

Use a having clause

SELECT u.uid, u.name 
FROM users u
LEFT OUTER JOIN categories c ON u.uid = c.uid
WHERE c.value = 'actor' OR c.value = 'musician'
GROUP BY u.uid
having count(distinct c.value) > 1

answered May 22, 2013 at 11:45

juergen d

205k40 gold badges304 silver badges377 bronze badges

Comments

Jens · Accepted Answer · 2013-05-22 11:48:09Z

1

If you really do not want to use having you could try this:

SELECT uid, name 
FROM users
WHERE 
uid IN (SELECT uid FROM categories WHERE value='actor')
AND uid IN (SELECT uid FROM categories WHERE value='musician')

But there is really nothing wrong with using HAVING ;)

answered May 22, 2013 at 11:48

Jens

2,0751 gold badge14 silver badges30 bronze badges

Collectives™ on Stack Overflow

MYSQL compare values from same columns

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related