list results in mysql with join [duplicate]

This question already has answers here: list results in mysql from the same table (2 answers) Closed 9 years ago.

How can i list the iduser from this table where gender=man and int=woman and age=19?

Table usermeta
----------------------
id  iduser a        b
12  204    age      19
7   203    age      35
6   200    age      24
3   201    age      34
5   201    gender   male
2   200    gender   female
8   203    gender   male
9   204   开发者_如何学Python gender   male
4   201    int      female
10  204    int      male
11  203    int      female
1   200    int      male

The answer should be:

iduser

204

I got this from another post...

SELECT a.`iduser` FROM `table` a 
JOIN `table` b ON b.`iduser` = a.`iduser` 
WHERE a.`a`='gender' AND a.`b`='man' AND b.`a`='int' AND b.`b`='woman'

but does not filter the age field...

Thanks.

---

Here's another alternative to throw into the mix:

SELECT user_id
  FROM wp_usermeta
  WHERE (meta_key = 'age' AND meta_value = '19')
    OR (meta_key = 'gender' AND meta_value = 'male')
    OR (meta_key = 'int' AND meta_value ='male')
  GROUP BY user_id
  HAVING COUNT(*) = 3

---

Looks like you need another self join which we'll alias as t3. Also, I'm changing the aliases from a,b to t1,t2 because your example is totally unreadable. The DISTINCT may or may not be necessary.

SELECT DISTINCT t1.`iduser`
FROM `table` t1
  JOIN `table` t2 ON t1.`iduser` = t2.`iduser` 
  JOIN `table` t3 ON t1.`iduser` = t3.`iduser` 
WHERE 
  t1.`a` = 'gender'
  AND t1.`b` = 'man'
  AND t2.`a` = 19
  AND t3.`b` = 'woman'

---

You can try something like this, but I think it will work slowly.

SELECT iduser, GROUP_CONCAT(CONCAT(a,':',b) SEPARATOR ',') as groups 
from TABLE
group by iduser
having LOCATE('gender:man', groups) != 0 and LOCATE('int:woman', groups) != 0 and LOCATE('age:19', groups) != 0

I didn't check this one on real data so if any errors occur put down them in comments.

Another way:

SELECT t1.iduser from table t1 
INNER JOIN table t2 on t1.iduser=t2.iduser and t1.a='age' and t2.a='gender'
INNER JOIN table t3 on t2.iduser = t3.iduser and t3.a='int'
where t1.b='19' and t2.b='man' and t3.b='woman';