 <p>It seems that you name table <code>products AS p</code> and then later also <code>product_fields AS p</code>. Try this:</p> <pre><code>SELECT p.*, c.value AS color, g.value AS gender, p.value AS price FROM products p LEFT JOIN product_fields c ON c.product_id = p.id AND c.name = 'color' LEFT JOIN product_fields g ON g.product_id = p.id AND g.name = 'gender' LEFT JOIN product_fields pr ON pr.product_id = p.id AND pr.name = 'price' </code></pre> <p>Note, that you use <code>LEFT JOIN</code>, which allows a match NULL values. maybe all you need to do is changing to <code>INNER JOIN</code>?</p> <pre><code>SELECT p.*, c.value AS color, g.value AS gender, p.value AS price FROM products p INNER JOIN product_fields c ON c.product_id = p.id AND c.name = 'color' INNER JOIN product_fields g ON g.product_id = p.id AND g.name = 'gender' INNER JOIN product_fields pr ON pp.product_id = p.id AND pp.name = 'price' </code></pre>