CODEWITHSUNDEEP
mysqlsql
Muhammad Khan
Muhammad Khan

Reputation: 136

How to inner join a lookup table for a mysql query?

I need help with a mysql query using the following two tables:

profiles (TABLE 1)

id  user_id gender  age height  bodytype
1   1         1     57    1        2
2   2         2     32    2        1

profile_lookup (TABLE 2)

id  option_group    option_value    option_name
1   gender                 1        Female
2   gender                 2        Male
3   gender                 3        Prefer not to say
4   height                 1        5 ft - 6  in
5   height                 2        5ft - 9 in
6   bodytype               1        Petite/slim
7   bodytype               2        Average

There are whole lot of other options and option values that i am omitting for the sake of brevity

I am interested to do inner join queries using the syntax as shown below:

SELECT * 
  FROM profiles 
 WHERE bodytype = 2 
  JOIN profile_lookup 
    ON profiles.gender = profile_lookup..... (not sure)

Request help with using the correct syntax using the above two tables. Thanks

Upvotes: 2

Views: 1624

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I think you want:

SELECT p.*, plg.option_name as gender
FROM profiles p INNER JOIN
     profile_lookup plg
     ON plg.option_group = 'gender' and
        plg.option_value = p.gender
WHERE p.bodytype = 2 ;

You can extend this to other columns. You might want a LEFT JOIN in case some values don't match (i.e. are NULL):

SELECT p.*, plg.option_name as gender, plh.option_name as height
FROM profiles p LEFT JOIN
     profile_lookup plg
     ON plg.option_group = 'gender' AND
        plg.option_value = p.gender LEFT JOIN
     profile_lookup plh
     ON plh.option_group = 'height' AND
        plh.option_value = p.height
WHERE p.bodytype = 2

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

The where clause should be after the JOIN clause 

SELECT * FROM profiles 
INNER JOIN profile_lookup ON profiles.gender = profile_lookup.option_value    
 and profile_lookup.option_group   = 'gender' 
WHERE profiles.bodytype = 2

and for the join you need the proper profile_lookup.option_value

Upvotes: 1

Related Questions