[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

Selecting empty fields using a join

CUSTOMFIELDS
================================
OUTLET_ID, CUSTOMFIELD_ID, CUSTOMFIELD_NAME, CUSTOMFIELD_DEFAULTVALUE RESPONSETYPE_ID

CUSTOMFIELD_CONTROLVALUES
================================
CUSTOMFIELDCONTROLVALUE_ID
CUSTOMFIELDCONTROLVALUE_VALUE
CUSTOMFIELD_ID

Dear Experts,

I'm trying to get all values from CustomField_Field whether they have a value in OutletCustomFieldValue_Value or not.

At the moment the query only returns those with a value in OutletCustomFieldValue_Value: 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

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')

Open in new window

0
nkewney
Asked:
nkewney
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need to put a LEFT JOIN instead of a INNER JOIN when there are "missing" rows...


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 LEFT JOIN
                      OutletCustomFieldValues ON OutletList.Outlet_ID = OutletCustomFieldValues.Outlet_ID LEFT OUTER JOIN
                      CustomFields ON OutletCustomFieldValues.CustomField_ID = CustomFields.CustomField_ID AND 
                      AccountOutletCustomFields.CustomField_ID = CustomFields.CustomField_ID
WHERE     (OutletList.Outlet_ID = '18453')

Open in new window

0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now