Which SQL join should I use here?

Question

I have got 2 SQL tables, one for user tables and one for all things about the user. (age, sex, etc)

user table:

id, username, mail, etc

second table

id, user_id, type_id, content

user table content:

1, knife, [email protected], etc ...

second table:

1, 1, 38 (age)
1, 3, Male

I would like to get the user who is between age 20-40 and who is male. I'm here now:

SELECT 
        *
FROM 
        felhasznalok_kieg_adat
        JOIN felhasznalok
                ON felhasznalok_kieg_adat.user_id = felhasznalok.id
WHERE 
   felhasznalok_kieg_adat.kieg_id = "22" 
   and felhasznalok_kieg_adat.tartalom < 21 
   and felhasznalok_kieg_adat.tartalom < 40

It shows me the results between ages 21 and 40, but How can I add that Male or female thing?

Answer 1

You have to join one time for the age filter, and a second time for the gender filter.

SELECT User.*
FROM User
    JOIN UserContent ageFilter
        ON ageFilter.user_id = User.id
        AND ageFilter.type_id = 1  -- use type_id for joining
    JOIN UserContent genderFilter
        ON genderFilter.user_id = User.id
        AND genderFilter.type_id = 3
WHERE (ageFilter.content < 21 AND ageFilter.content < 40) -- what type is content? You have to make some sort of conversion here!
AND (genderFilter.content = 'Male')

I have replaced the name of tables and columns by what you have described in your question.
Add the type_id in the join, it becomes much more readeable this way.