?
Solved

Selecting empty fields using a join

Posted on 2007-12-06
1
Medium Priority
?
213 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

765 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