Solved

Selecting empty fields using a join

Posted on 2007-12-06
1
201 Views
Last Modified: 2012-05-05
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
Comment
Question by:nkewney
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20420208
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question