nkewney
asked on
Selecting empty fields using a join
CUSTOMFIELDS
========================== ======
OUTLET_ID, CUSTOMFIELD_ID, CUSTOMFIELD_NAME, CUSTOMFIELD_DEFAULTVALUE RESPONSETYPE_ID
CUSTOMFIELD_CONTROLVALUES
========================== ======
CUSTOMFIELDCONTROLVALUE_ID
CUSTOMFIELDCONTROLVALUE_VA LUE
CUSTOMFIELD_ID
Dear Experts,
I'm trying to get all values from CustomField_Field whether they have a value in OutletCustomFieldValue_Val ue or not.
At the moment the query only returns those with a value in OutletCustomFieldValue_Val ue: for example:
Terminal ID 22982497
Machine ID SM0002
Machine Type 39
Router Model 41
Router Position Left of fusebox - strapped to base
Comp PSI 44
Serial #
Manufactured
^ -- these two do not appear in the query and I can't find a combination of joins to do this.
Here's the query. Could anybody help me out?
Thanks
Nick
==========================
OUTLET_ID, CUSTOMFIELD_ID, CUSTOMFIELD_NAME, CUSTOMFIELD_DEFAULTVALUE RESPONSETYPE_ID
CUSTOMFIELD_CONTROLVALUES
==========================
CUSTOMFIELDCONTROLVALUE_ID
CUSTOMFIELDCONTROLVALUE_VA
CUSTOMFIELD_ID
Dear Experts,
I'm trying to get all values from CustomField_Field whether they have a value in OutletCustomFieldValue_Val
At the moment the query only returns those with a value in OutletCustomFieldValue_Val
Terminal ID 22982497
Machine ID SM0002
Machine Type 39
Router Model 41
Router Position Left of fusebox - strapped to base
Comp PSI 44
Serial #
Manufactured
^ -- these two do not appear in the query and I can't find a combination of joins to do this.
Here's the query. Could anybody help me out?
Thanks
Nick
SELECT CustomFields.CustomField_Field AS f, OutletCustomFieldValues.OutletCustomFieldValue_Value AS v
FROM AccountOutlets INNER JOIN
OutletList ON AccountOutlets.Outlet_ID = OutletList.Outlet_ID INNER JOIN
AccountOutletCustomFields ON AccountOutlets.Account_ID = AccountOutletCustomFields.Account_ID INNER JOIN
OutletCustomFieldValues ON OutletList.Outlet_ID = OutletCustomFieldValues.Outlet_ID RIGHT OUTER JOIN
CustomFields ON OutletCustomFieldValues.CustomField_ID = CustomFields.CustomField_ID AND
AccountOutletCustomFields.CustomField_ID = CustomFields.CustomField_ID
WHERE (OutletList.Outlet_ID = '18453')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.