I need to "Select ALL rows from table1, & if exists, associated rows from table2". My problem is similar to the question: "Show me all customers and associated orders, including customers with no orders". Some rows will have customer details but no order details. My situation and background are explained in detail below to help you understand.
Background
• Two large lookup (fact-less fact) tables – lu_house and lu_customer.
• lu_house has a house_cnt column and lu_cust has a cust_cnt column. For each record, a value of “1” is placed in the column.
• There is a One to Zero/Many relationship between House and Customer – One House can have Zero, One, or Many Customers.
• House statuses are Never, Active, or Disconnect.
• Customer statuses are Active or Disconnect.
• A Never house has never had a customer (zero customer records).
• Disconnect and Active houses have had one or more customers (one or more customer records).
• Only the most recent customer in a house may be active. The previous are disconnected.
• The house-customer status combinations are Never-NULL, Active-Active, Active-Disconnect, and Disconnect-Disconnect.
• lu_house contains one record for each address where service could be provided.
• house_id is contained in lu_house and lu_customer. (e.g., 111111, 111112, 111113, etc.)
• Customer number increments for each new customer in a house (e.g., 01, 02, 03, etc.)
• House dimension contains house level attributes.
• Customer dimension contains customer level attributes.
• For counting house, metric formulas are either sum(house_cnt) or count(house).
• For counting customers, metric formuals are either sum(cust_cnt) or count(cust).
Problem
• I am selecting from two dimensions/tables – house and customer.
• I am selecting based on some criteria which is irrelevant. The criteria are in the report filter.
• I need to select all houses and the corresponding customer if they exist.
• The Never houses will have no customer records, while the Disconnect and Active houses will have one or more customer records.
• In reality, I only need the max customer in the house (whether it be disconnect or active), but that is another issue.
Here is some already joined sample data:
house_id, house_stat, house_type, cust_id, cust_stat, cust_name, Select_this_row?
111111, Never, Single Family, NULL, NULL, NULL, Yes
111112, Active, Single Family, 01, Active, Joe, Yes
111113, Active, Apartment, 01, Disconnect, James, No
111113, Active, Apartment, 02, Disconnect, Jack, No
111113, Active, Apartment, 03, Active, Jason, Yes
111114, Disconnect, Single Family, 01, Disconnect, Jeremy, Yes
111115, Never, Single Family, NULL, NULL, NULL, Yes
111116, Disconnect, Apartment, 01, Disconnect, Jim, No
111116, Disconnect, Apartment, 02, Disconnect, John, Yes
There are also nonsensical keys for both the house and cust table.
Here is a simple SQL Server select which works and provides me with exactly what I need:
SELECT house.house_id, house.house_stat, house.type, cust.cust_id, cust.cust_stat, cust.cust_name
FROM house LEFT OUTER JOIN cust ON house.house_id = cust.house_id
Production environment
• MSI 7.5.2
• Red Brick DW
I have looked through and tried many of the prevous Tek-Tips, read MSI Tech Notes, and tried all of the report variations, metric settings, and VLDB settings I could think of. This should be possible. Am I correct?
Mark
Background
• Two large lookup (fact-less fact) tables – lu_house and lu_customer.
• lu_house has a house_cnt column and lu_cust has a cust_cnt column. For each record, a value of “1” is placed in the column.
• There is a One to Zero/Many relationship between House and Customer – One House can have Zero, One, or Many Customers.
• House statuses are Never, Active, or Disconnect.
• Customer statuses are Active or Disconnect.
• A Never house has never had a customer (zero customer records).
• Disconnect and Active houses have had one or more customers (one or more customer records).
• Only the most recent customer in a house may be active. The previous are disconnected.
• The house-customer status combinations are Never-NULL, Active-Active, Active-Disconnect, and Disconnect-Disconnect.
• lu_house contains one record for each address where service could be provided.
• house_id is contained in lu_house and lu_customer. (e.g., 111111, 111112, 111113, etc.)
• Customer number increments for each new customer in a house (e.g., 01, 02, 03, etc.)
• House dimension contains house level attributes.
• Customer dimension contains customer level attributes.
• For counting house, metric formulas are either sum(house_cnt) or count(house).
• For counting customers, metric formuals are either sum(cust_cnt) or count(cust).
Problem
• I am selecting from two dimensions/tables – house and customer.
• I am selecting based on some criteria which is irrelevant. The criteria are in the report filter.
• I need to select all houses and the corresponding customer if they exist.
• The Never houses will have no customer records, while the Disconnect and Active houses will have one or more customer records.
• In reality, I only need the max customer in the house (whether it be disconnect or active), but that is another issue.
Here is some already joined sample data:
house_id, house_stat, house_type, cust_id, cust_stat, cust_name, Select_this_row?
111111, Never, Single Family, NULL, NULL, NULL, Yes
111112, Active, Single Family, 01, Active, Joe, Yes
111113, Active, Apartment, 01, Disconnect, James, No
111113, Active, Apartment, 02, Disconnect, Jack, No
111113, Active, Apartment, 03, Active, Jason, Yes
111114, Disconnect, Single Family, 01, Disconnect, Jeremy, Yes
111115, Never, Single Family, NULL, NULL, NULL, Yes
111116, Disconnect, Apartment, 01, Disconnect, Jim, No
111116, Disconnect, Apartment, 02, Disconnect, John, Yes
There are also nonsensical keys for both the house and cust table.
Here is a simple SQL Server select which works and provides me with exactly what I need:
SELECT house.house_id, house.house_stat, house.type, cust.cust_id, cust.cust_stat, cust.cust_name
FROM house LEFT OUTER JOIN cust ON house.house_id = cust.house_id
Production environment
• MSI 7.5.2
• Red Brick DW
I have looked through and tried many of the prevous Tek-Tips, read MSI Tech Notes, and tried all of the report variations, metric settings, and VLDB settings I could think of. This should be possible. Am I correct?
Mark