CODEWITHSUNDEEP
sqlpostgresql
Rakesh K
Rakesh K

Reputation: 712

Join with 2 temporary table

How to create one more temporary table in below query to have join with the same.

WITH financial_move_out_due_days AS ( 
    SELECT property_id, 
        management_company_id, 
        value 
    FROM property_preferences 
    WHERE property_id IN ( 112,332 ) AND management_company_id = 23 
    )
    SELECT 
        l.id,
        l.primary_customer_id,
        ( c.name_first || c.name_last ) AS customer_name_full,
        c.email_address AS customer_email_address,
        c.phone_number AS phone_number
    FROM leases l
    JOIN customers c ON ( c.management_company_id = l.management_company_id AND c.id = l.primary_customer_id )
    JOIN financial_move_out_due_days fmpodd ON ( fmpodd.management_company_id = l.management_company_id AND fmpodd.property_id = l.property_id )
    WHERE
        l.management_company_id = 23
        AND l.property_unit_id IS NOT NULL
        AND l.unit_space_id IS NOT NULL';

Upvotes: 1

Views: 138

Answers (2)

Ramesh Mhetre
Ramesh Mhetre

Reputation: 404

Try like this:

   WITH financial_move_out_due_days AS 
    ( 
        SELECT property_id, 
            management_company_id, 
            value 
        FROM property_preferences 
        WHERE property_id IN ( 112,332 ) AND management_company_id = 23 
    ),
    another_financial_move_out_due_days AS 
    ( 
        SELECT property_id, 
            management_company_id, 
            value 
        FROM property_preferences 
        WHERE property_id IN ( 112,332 ) AND management_company_id = 23 
    )
    SELECT 
        l.id,
        l.primary_customer_id,
        ( c.name_first || c.name_last ) AS customer_name_full,
        c.email_address AS customer_email_address,
        c.phone_number AS phone_number
    FROM leases l
    JOIN customers c ON ( c.management_company_id = l.management_company_id AND c.id = l.primary_customer_id )
    JOIN financial_move_out_due_days fmpodd ON ( fmpodd.management_company_id = l.management_company_id AND fmpodd.property_id = l.property_id )
    JOIN another_financial_move_out_due_days fmpodd ON ( fmpodd.management_company_id = l.management_company_id AND fmpodd.property_id = l.property_id )
    WHERE
        l.management_company_id = 23
        AND l.property_unit_id IS NOT NULL
        AND l.unit_space_id IS NOT NULL';

Upvotes: 1

realnumber3012
realnumber3012

Reputation: 1062

WITH FirstTable AS
(
),
SecondTable AS
(
),
....

Upvotes: 0

Related Questions