Solved

Selecting empty fields using a join

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query output (script) from a stored procedure 4 38
SQL Syntax 6 41
Applying Roles in Common Scenarios 3 19
UPDATE JOIN multiple tables 5 22
I have a large data set and a SSIS package. How can I load this file in multi threading?
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

733 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