sql:JOIN with WHERE for 2nd table, include rows not in 2nd table yet?

[carsten888]

I trying to fix this query. 2 tables are joined, which works fine. I filter the results with WHERE AND, but if the row in the 2nd table does not exist yet, the join is not in the results. I want to filter on second_table.item_id and include it if it does not exist yet (see AND statement).

"SELECT f.*, v.*, v.id AS value_id"
. "\nFROM #__pi_custom_fields AS f"
. "\nLEFT JOIN #__pi_custom_fields_values AS v"
. "\nON f.id=v.field_id"
. "\nWHERE f.type_id='$type_id'"
. "\nAND (v.item_id='$item_id' OR v.id=0)"
. "\nORDER BY f.ordering ASC"

[carsten888]

ahum, let me refine that.
I just noticed that the whole thing does not seem to behave as a LEFT JOIN but instead as a RIGHT JOIN, because it includes the right tables rows, while I only want the left tables.

see tut [w3schools.com...]

"SELECT *"
. "\nFROM #__pi_custom_fields AS f"
. "\nLEFT OUTER JOIN #__pi_custom_fields_values AS v"
. "\nON f.id=v.field_id"
foreach($fields as $field){echo 'fieldname='.$field->name.' value='.$field->value.'}

the loop should return me a list of the fields with values if they are in db:
fieldname=field1 value=something
fieldname=field2 value=
fieldname=field3 value=somethingelse

but instead it outputs:
fieldname=field1 value=something
fieldname=field1 value=something
fieldname=field3 value=somethingelse
fieldname=field3 value=somethingelse
fieldname=field3 value=somethingelse

want am I overlooking here?