title: Getting data from 3 MySQL tables
body: <p>I'm having a struggle here trying to grab some data from 3 database tables..</p>
  
  <p>The idea is that users can fill out their profile with several fields, and I'm storing every profile field, field values and the users selected value in separate tables.</p>
  
  <p>The structure of the tables look like this:</p>
  
  <pre><code>Table 'profile_fields'
  - id
  - name
  - sort
  - status (enum '0', '1')
  
  Table 'profile_field_values'
  - id
  - profile_field_id
  - name
  
  Table 'user_profile_fields'
  - user_id
  - profile_field_id
  - profile_field_value_id
  </code></pre>
  
  <p>Would be really nice if you could tell me how to construct this query, and why you used the JOIN you did.</p>
  
  <p>Also, how would this table layout scale when the userbase grows?</p>
  
  <p>Thank you so much in advance!</p>
  
  <p>Edit:
  OK, I still can't figure out how to make it return all the fields from 'profile_fields' along with the users selected option from 'user_profile_fields'. If the user hasn't selected a value, it should just be null.</p>
  
  <p>This is my (non-functional) query so far:</p>
  
  <pre><code>SELECT  PF.id AS field_id, PF.name AS field_name, UPF.profile_field_value_id AS value_id, PF.type, PFV.name 
  FROM  profile_fields  PF
  LEFT JOIN profile_fields_values PFV ON PFV.profile_field_id = PF.id
  LEFT JOIN user_profile_fields UPF ON UPF.user_id=1  AND PF.id = UPF.profile_field_id
  WHERE length(PF.name) &gt; 0 and PF.status = '1'
  ORDER BY PF.sort
  </code></pre>
  
  <p>This query seems to work, but it does not fetch the name of the value from 'profile_field_values':</p>
  
  <pre><code>SELECT PF.id AS field_id, PF.name AS field_name, UPF.profile_field_value_id AS value_id, PF.type
  FROM profile_fields PF
  LEFT JOIN user_profile_fields UPF ON UPF.user_id =1
  AND PF.id = UPF.profile_field_id
  WHERE LENGTH( PF.name ) &gt;0
  AND PF.status =  '1'
  ORDER BY PF.sort
  </code></pre>