totally stuck with join query

[carsten888]

I am totally stuck with a query.

$database->setQuery( "SELECT f.*, v.* "
. "\n FROM #__pi_custom_fields AS f "
. "\n LEFT JOIN #__pi_custom_fields_values AS v "
. "\n ON f.id=v.field_id "
. "\n WHERE f.type_id='$type_id' "
. "\n AND v.item_id='$id' "
. "\n ORDER BY f.ordering ASC "
);

So it is a Join query with 2 tables, one defining fields (in a form) the other for the values. I used join left because it would still include the fields if there are no values, but because the values are also restricted (AND v.item_id='$id') empty values are not included.

How would I write something like:

"\n AND if(v.item_id){v.item_id='$id'//value exists, but only include if id matches}else{//value does not exist, but include the thing} "

anyone?

[LifeinAsia]

Try:
"\n AND (v.item_id='$id' OR v.item_id IS NULL)"

[edited by: LifeinAsia at 8:39 pm (utc) on Oct. 8, 2008]

[carsten888]

thank you.

late last night I managed to do this with:
. "\n ON f.id=v.field_id AND v.item_id='$id'"

thanks for the reply.