Hello. In doing a left join with a particular view as the right table, and non-matching join criteria, I am getting values returned in a few fields. All the rest are NULL. I would expect all the right side values to be NULL.
(The view is large and messy, but it doesn't seem like that should matter. I've attached the create statement for the view anyway.)
Am I missing something really really obvious about LEFT JOINs here? This statement seems to confirm my expectations:
Thanks in advance.
Ken
-- Nothing special about the -1 id here, just an example of a non-matching value.
ag_spc=> SELECT foo.client_id AS foo_id,rent_info.client_id AS ri_id,move_in_type,annual_income,monthly_income_total,dependent_count from (SELECT -1 AS client_id) foo LEFT JOIN rent_info USING (client_id);
foo_id | ri_id | move_in_type | annual_income | monthly_income_total | dependent_count
--------+-------+--------------+---------------+----------------------+-----------------
-1 | | Move-in | 0 | 0 | 0
(1 row)
ag_spc=> SELECT * FROM rent_info WHERE client_id = -1;
effective_date | effective_date_end | rent_amount_tenant_calculated | rent_amount_tenant | rent_amount_spc | project_date | own_date | is_active_manual | residence_own_id | client_id | housing_project_code | housing
_project_label | housing_unit_code | residence_date | residence_date_end | was_received_hap | was_received_compliance | moved_from_code | chronic_homeless_status_code | move_in_type | lease_on_file | moved_to_code |
moved_to_unit | departure_type_code | departure_reason_code | move_out_was_code | returned_homeless | unit_rent_manual | tenant_pays_deposit | comment_damage | comment_deposit | comment | income_id | income_date | in
come_date_end | annual_income | monthly_income_total | monthly_income_primary | income_primary_code | monthly_income_secondary | income_secondary_code | monthly_income_tertiary | income_tertiary_code | monthly_intere
st_income | other_assistance_codes | income_certification_type_code | child_care | handicap_assistance | medical_expense | fund_type_code | rent_date_effective | rent_date_end | housing_unit_id | housing_unit_label |
housing_unit_date | housing_unit_date_end | unit_type_code | tax_credit | max_occupant | alternate_address_id | mailing_address_unit | street_address | mailing_address_client | housing_unit_subsidy_id | housing_unit
_subsidy_date | housing_unit_subsidy_date_end | unit_subsidy_amount | unit_rent | tenant_vendor_number | vendor_number | utility_allowance_manual | utility_allowance_unit | utility_allowance | utility_allowance_code
| security_deposit | fair_market_rent | rent_amount_tenant_manual | dependent_count | cid | reg_spc_date | reg_spc_date_end | grant_number_code | agency_code | agency_phone | agency_label | agency_contact | fake_key
----------------+--------------------+-------------------------------+--------------------+-----------------+--------------+----------+------------------+------------------+-----------+----------------------+--------
---------------+-------------------+----------------+--------------------+------------------+-------------------------+-----------------+------------------------------+--------------+---------------+---------------+-
--------------+---------------------+-----------------------+-------------------+-------------------+------------------+---------------------+----------------+-----------------+---------+-----------+-------------+---
--------------+---------------+----------------------+------------------------+---------------------+--------------------------+-----------------------+-------------------------+----------------------+---------------
----------+------------------------+--------------------------------+------------+---------------------+-----------------+----------------+---------------------+---------------+-----------------+--------------------+
-------------------+-----------------------+----------------+------------+--------------+----------------------+----------------------+----------------+------------------------+-------------------------+-------------
--------------+-------------------------------+---------------------+-----------+----------------------+---------------+--------------------------+------------------------+-------------------+------------------------
+------------------+------------------+---------------------------+-----------------+-----+--------------+------------------+-------------------+-------------+--------------+--------------+----------------+----------
(0 rows)
AGENCY Software
A data system that puts you in control
100% Free Software
(253) 245-3801
learn more about AGENCY or
follow the discussion.
CREATE OR REPLACE VIEW rent_info AS
SELECT
greatest(income_date,residence_date,housing_unit_date,housing_unit_subsidy_date) AS effective_date,
-- least(COALESCE(income_date_end,current_date),COALESCE(residence_date_end,current_date),COALESCE(housing_unit_date_end,current_date),COALESCE(housing_unit_subsidy_date_end,current_date)) AS effective_date_end,
least(income_date_end,residence_date_end,housing_unit_date_end,housing_unit_subsidy_date_end) AS effective_date_end,
-- Monthly Rent
ROUND((
-- Monthly Rent
(
-- Adjusted Annual Income
GREATEST(
annual_income
-- Disability Deduction
- 400
-- $480 / Dependent
- (dependent_count * 480)
-- Childcare
- COALESCE(child_care,0)
-- Medical expenses over 3% of income
- GREATEST(
--COALESCE(medical_expense,0)-annual_income,0)*0.03
COALESCE(medical_expense,0)-annual_income*0.03,0)
,0) -- Adjusted Annual Income
/ 12 * 0.3
- COALESCE(utility_allowance,0)
) :: decimal(7,2))) AS rent_amount_tenant_calculated,
/*
ROUND((
-- Adjusted Annual Income
(
annual_income
-- Disability Deduction
- 400
-- $480 / Dependent
- (dependent_count * 480)
-- Childcare
- COALESCE(child_care,0)
-- Medical expenses over 3% of income
- GREATEST(
COALESCE(medical_expense,0)-annual_income,0)*0.03
) -- Adjusted Annual Income
/ (12 * 0.3)
- COALESCE(utility_allowance,0)
) :: decimal(6,2)) AS rent_amount_tenant_calculated,
*/
-- Repeat all of above
ROUND(COALESCE(rent_amount_tenant_manual,
-- Monthly Rent
(
-- Adjusted Annual Income
GREATEST(
annual_income
-- Disability Deduction
- 400
-- $480 / Dependent
- (dependent_count * 480)
-- Childcare
- COALESCE(child_care,0)
-- Medical expenses over 3% of income
- GREATEST(
-- COALESCE(medical_expense,0)-annual_income,0)*0.03
COALESCE(medical_expense,0)-annual_income*0.03,0)
,0) -- Adjusted Annual Income
/ 12 * 0.3
- COALESCE(utility_allowance,0)
)::decimal(7,2))) AS rent_amount_tenant,
unit_rent -
-- Repeat all of above (again)
GREATEST(ROUND(COALESCE(rent_amount_tenant_manual,
-- Monthly Rent
(
-- Adjusted Annual Income
GREATEST(
annual_income
-- Disability Deduction
- 400
-- $480 / Dependent
- (dependent_count * 480)
-- Childcare
- COALESCE(child_care,0)
-- Medical expenses over 3% of income
- GREATEST(
-- COALESCE(medical_expense,0)-annual_income,0)*0.03
COALESCE(medical_expense,0)-annual_income*0.03,0)
,0) -- Adjusted Annual Income
/ 12 * 0.3
- COALESCE(utility_allowance,0)
) :: decimal(7,2))),0) AS rent_amount_spc,
*,
client_id::text || '_' ||
greatest(income_date,residence_date,housing_unit_date,housing_unit_subsidy_date)::text
AS fake_key
/*
reg_spc_date,
reg_spc_date_end,
grant_number_code,
agency_code
*/
FROM
(
SELECT
continuous_housing_project_own(a.client_id) AS project_date,
continuous_housing_own(a.client_id) AS own_date,
a.is_active_manual,
a.residence_own_id,
a.client_id,
a.housing_project_code,
lhp.description AS housing_project_label,
a.housing_unit_code,
a.residence_date,
a.residence_date_end,
a.was_received_hap,
a.was_received_compliance,
a.moved_from_code,
a.chronic_homeless_status_code,
a.move_in_type,
a.lease_on_file,
a.moved_to_code,
a.moved_to_unit,
a.departure_type_code,
a.departure_reason_code,
a.move_out_was_code,
a.returned_homeless,
a.unit_rent_manual,
a.tenant_pays_deposit,
a.comment_damage,
a.comment_deposit,
a.comment,
inc.income_id,
inc.income_date,
inc.income_date_end,
inc.annual_income,
inc.monthly_income_total,
inc.monthly_income_primary,
inc.income_primary_code,
inc.monthly_income_secondary,
inc.income_secondary_code,
inc.monthly_income_tertiary,
inc.income_tertiary_code,
inc.monthly_interest_income,
inc.other_assistance_codes,
inc.income_certification_type_code,
inc.child_care,
inc.handicap_assistance,
inc.medical_expense,
inc.fund_type_code,
inc.rent_date_effective,
inc.rent_date_end,
hu.housing_unit_id,
hu.housing_unit_label,
hu.housing_unit_date,
hu.housing_unit_date_end,
hu.unit_type_code,
hu.tax_credit,
hu.max_occupant,
hu.alternate_address_id,
hu.mailing_address AS mailing_address_unit,
hu.street_address,
COALESCE (name_prefix || ' ','')
|| name_first || ' '
|| COALESCE(name_middle || ' ','')
|| name_last
|| E'\n'
|| hu.mailing_address AS mailing_address_client,
hus.housing_unit_subsidy_id,
hus.housing_unit_subsidy_date,
hus.housing_unit_subsidy_date_end,
hus.unit_subsidy_amount,
COALESCE(a.unit_rent_manual,hus.unit_subsidy_amount) AS unit_rent,
-- COALESCE(a.tenant_vendor_number_manual,(SELECT tenant_vendor_number FROM client WHERE client_id=a.client_id)) AS tenant_vendor_number,
--COALESCE(a.tenant_vendor_number_manual,tenant_vendor_number) AS tenant_vendor_number,
client.tenant_vendor_number,
lhp.vendor_number,
a.utility_allowance_manual,
hus.utility_allowance AS utility_allowance_unit,
COALESCE(a.utility_allowance_manual,hus.utility_allowance) AS utility_allowance,
hus.utility_allowance_code,
hus.security_deposit,
hus.fair_market_rent,
inc.rent_amount_tenant_manual,
(SELECT COUNT(*) FROM family_member fm LEFT JOIN client USING (client_id)
WHERE fm.household_head_id=a.client_id
AND family_member_date <= COALESCE(a.residence_date_end,family_member_date)
AND COALESCE(fm.family_member_date_end,residence_date) >= a.residence_date
AND fm.client_id!=a.client_id
AND (
COALESCE(is_dependent_manual,false)
OR (
(COALESCE(is_dependent_manual,true)) AND
(fm.family_relation_code IN ('GRANDDAUGH','GRANDSON','DAUGHTER','SON')
AND ( ((inc.income_date - dob)/365.25) < 18)
)
)
)
) AS dependent_count
FROM residence_own AS a
LEFT JOIN client USING (client_id),
housing_unit_subsidy hus
LEFT JOIN housing_unit hu USING (housing_unit_code,housing_project_code)
LEFT JOIN l_housing_project lhp USING (housing_project_code),
-- housing_unit AS hu LEFT JOIN l_housing_project lhp USING (housing_project_code),
-- housing_unit_subsidy AS hus,
income AS inc
WHERE
--ri.is_active_manual
--
--AND hu.housing_unit_code = a.housing_unit_code
hus.housing_unit_code = a.housing_unit_code
--AND hus.housing_project_code = a.housing_project_code
--AND hu.housing_project_code = a.housing_project_code
AND inc.client_id = a.client_id
AND inc.income_date <= COALESCE(a.residence_date_end,inc.income_date)
AND COALESCE(inc.income_date_end,a.residence_date) >= a.residence_date
AND hus.housing_unit_subsidy_date <= COALESCE(a.residence_date_end,hus.housing_unit_subsidy_date)
AND COALESCE( hus.housing_unit_subsidy_date_end,a.residence_date) >= a.residence_date
AND hu.housing_unit_date <= COALESCE(a.residence_date_end,hus.housing_unit_subsidy_date)
AND COALESCE( hu.housing_unit_date_end,a.residence_date) >= a.residence_date
) foo
-- LEFT JOIN (SELECT * FROM reg_spc) rs ON (rs.client_id=foo.client_id AND effective_date BETWEEN reg_spc_date AND COALESCE(reg_spc_date_end,effective_date,current_date))
LEFT JOIN (SELECT client_id AS cid,reg_spc_date,reg_spc_date_end,grant_number_code,agency_code,phone AS agency_phone,la.description AS agency_label,la.contact AS agency_contact FROM reg_spc LEFT JOIN l_agency la USING (agency_code)) rs ON (rs.cid=foo.client_id AND
greatest(income_date,residence_date,housing_unit_date,housing_unit_subsidy_date) -- effective_date,
BETWEEN reg_spc_date AND COALESCE(reg_spc_date_end,greatest(income_date,residence_date,housing_unit_date,housing_unit_subsidy_date))
)
WHERE
COALESCE(income_date_end,residence_date_end,housing_unit_date_end,housing_unit_subsidy_date_end) IS NULL OR
least(income_date_end,residence_date_end,housing_unit_date_end,housing_unit_subsidy_date_end)
>
greatest(income_date,residence_date,housing_unit_date,housing_unit_subsidy_date)
;
CREATE OR REPLACE view rent_info_current AS
--SELECT DISTINCT ON (client_id) * FROM rent_info ORDER BY client_id,effective_date DESC;
SELECT * FROM rent_info WHERE effective_date <= target_date() AND COALESCE(effective_date_end,target_date()) >= target_date() ORDER BY effective_date DESC;
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
