title: mysql join tables with an if condition?
body: <p>Im trying to join 5 tables that look somewhat like this</p>
  
  <p><strong>post table</strong></p>
  
  <p>ID | product | user-us | make-id | dealer-id |  pending | .... 30 other columns ... |</p>
  
  <p><strong>make table</strong></p>
  
  <p>ID | make |</p>
  
  <p><strong>state table</strong></p>
  
  <p>prefix | state | city | zip</p>
  
  <p><strong>members table</strong></p>
  
  <p>ID | name | email | password | zip | dealer-id</p>
  
  <p><strong>dealer table</strong></p>
  
  <p>ID | dealer name | city | state | zip | address | phone </p>
  
  <p>MySql query looks like this</p>
  
  <pre><code>    SELECT *
      FROM `post` AS p
          JOIN (`make` AS m, `state` AS s, `members` AS mb, `dealer` AS d)
          ON (p.Make = m.id AND p.state = s.id AND p.id = mb.id AND mb.dealer-id = d.id)
     WHERE p.pending != '1'
  </code></pre>
  
  <p>The problem is this query only returns rows that <strong>member.dealer-id = dealer.id</strong>,  And if i use a LEFT JOIN, it returns all the correct rows, BUT all columns in tables make, state, members and dealer will be NULL. which it shouldn't be because i need the info in those tables.</p>
  
  <p>Is there away i can only join the <code>dealer</code> table if <strong>member.dealer-id is > 0</strong>? i could add a row in the dealer table with id 0, but there has to be a better way.</p>