Solved

Selecting empty fields using a join

Posted on 2007-12-06
1
188 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 142

Accepted Solution

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now