How to select a single value from a column based on multiple values from another column in SQL/php

Question

The database is not mine. The structure is

fid| uid | value
 3 |  3  | spain
 3 |  5  | France
 2 |  3  | 45
 6 |  3  | male
 6 |  5  | female
 2 |  5  | 32

The field ID is primary key in another table, I'd like to forget about. Im trying to find all uid that have values 'spain', 'male'

I have the following working.

SELECT uid 
FROM DATABASE
WHERE value IN ('spain', 'male') GROUP BY uid HAVING COUNT(*) >= 2

The catch is as follows.. How would I select male, spain with value in range of 20-30 (that is males born in spain aged between 20-30?

Thanks a ton!!

Answer 1

You have a really messed up table. You are mixing unrelated things in value.

Try:

SELECT uid
FROM DATABASE a JOIN
  DATABASE b USING (uid)
WHERE a.value IN ('spain', 'male')
  AND b.value >= 20
  AND b.value <= 30
GROUP BY uid
HAVING COUNT(*) >= 2

Note that I am comparing a string and an integer. You will need to test to see how well that works.

Also, I just put spain and male together, but perhaps they are really unrelated?

Does fid determine the type of value?

Try:

SELECT uid
FROM DATABASE country
JOIN DATABASE gender USING (uid)
JOIN DATABASE age USING (uid)
WHERE
  country.fid = 3 AND
  gender.fid = 6 AND
  age.fid = 2 AND
  county.value = 'spain' AND
  gender.value = 'male' AND
  age.value >= 20
  age.value <= 30
GROUP BY uid
HAVING COUNT(*) >= 2

This code should work more reliably.