title: MySQL INNER JOIN on the same tables
body: <p>I have a system where all the users are stored in the same table and their "role" decides what kind of user they are.</p>
  
  <p>Customers earn points at stores. I am trying to create a PHP page where the customer can see which store they have earned their points and so stores can log in and see which Customers have earned points. (Hope that makes sense!)</p>
  
  <p>I have two tables</p>
  
  <p>Table1 is the user table and contains all the user information in it.. i.e Name, Address, etc</p>
  
  <p>Table2 stores all the points and the uid (which is the customer ID) and the SID (which is the store id) both these fields match the uid in the user table.</p>
  
  <p>sooo....</p>
  
  <p>I think the MySQL needs to look something like this</p>
  
  <pre><code>Select loyal_tokens.tokens, loyal_tokens.date, users.id, users.email, users.first_name, users.last_name,loyal_tokens.sid,loyal_tokens.uid from users, loyal_tokens
  INNER JOIN loyal_tokens, users as StoresName on users.id = loyal_tokens.sid 
  INNER JOIN loyal_tokens, users as UsersName on users.id = loyal_tokens.uid 
  </code></pre>
  
  <p>This doesn't work (obviously as there are non-unique tables). I am really at a loss on how to fix it!</p>
  
  <p>At the end of the SQL, I will be putting a where clause</p>
  
  <pre><code>WHERE users.id=x
  </code></pre>
  
  <p>The user id could be either a store or a customer and it will display the points that they have earned and display the store/customer name rather than the ID. </p>
  
  <p>Hope that makes sense!</p>
  
  <p>Thanks</p>
  
  <p>Michael</p>