CrunchyToast CrunchyToast - 1 year ago 81
MySQL Question

Show unmatched/non-existent rows with LEFT JOIN

I'm having an issue with a SQL query using

. I have a table of custom fields and a table of values for those fields. I joined the tables to get the values, but it's only showing data from matched rows. If the value row is non-existent, it does not show the field name. My goal is to show all the fields regardless of if they have a value or not, but still have existing values matched to its field. Below is my query.

$customfields = $pdo->prepare("SELECT * FROM fields f LEFT JOIN fields_values v ON = v.fieldid WHERE v.related_system=:relsystem ORDER BY f.fieldorder ASC");
$customfields->bindParam(':relsystem', $_GET['id'], PDO::PARAM_STR);
$customfields = $customfields->fetchAll(PDO::FETCH_ASSOC);

After the query I have a

foreach ($customfields as $fields) {
$ftype = $fields['fieldtype'];
$fname = $fields['fieldname'];
$fvalue = $fields['value'];

echo $fname;
echo $fvalue;

This again only shows matched records. Fields that have no value row do not display.

Answer Source

You are negating your outer join with the where criteria. Instead move it to the join criteria:

FROM fields f 
    LEFT JOIN fields_values v ON = v.fieldid 
         AND v.related_system=:relsystem 
ORDER BY f.fieldorder
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download