Left join with complex join clause

Question

Hi,

I have two tables and want to left join them. I want all entries from the account table, but only rows matching a criteria from the right table. If no criteria is matching, I only want the account.

The following does not work as expected:

SELECT * FROM Account a
LEFT JOIN 
 Entries ef ON ef.account_id = a.account_id AND
(ef.entry_period_end_date BETWEEN $periodStartDate_escaped AND LAST_DAY(date_add( $periodStartDate_escaped, INTERVAL $periodLengthInMonths_escaped MONTH)) 
OR
ef.forecast_period_end BETWEEN $periodStartDate_escaped AND LAST_DAY(date_add( $periodStartDate_escaped, INTERVAL $periodLengthInMonths_escaped MONTH))
OR
ef.entry_period_end_date IS NULL 
OR 
ef.forecast_period_end IS NULL
)

cause it also gives me the rows from the entries table, which are outside the requested period.

Example Data:

Account Table
AccountID | AccountName
1           Test
2           Foobar
3           Test1
4           Foobar2


Entries Table
id | AccountID | entry_period_end_date | forecast_period_end | amount
1    1           12/31/2009              12/31/2009            100
2    1           NULL                    10/31/2009            150
3    2           NULL                    NULL                  200
4    3           10/31/2009              NULL                  250
5    4           10/31/2009              10/31/2009            300

So the query should return (when i set startDate = 12/01/2009, endDate 12/31/2009)

AccountID | id
1           1
2           NULL
3           NULL
4           NULL

Thx, Martin

Answer 1

If either entry_period_end_date or forecast_period_end is NULL, the row will be returned, even if your other, non-NULL column is not within the period.

Probably you meant this:

SELECT  *
FROM    Account a
LEFT JOIN 
        Entries ef
ON     ef.account_id = a.account_id
       AND
       (
       entry_period_end_date BETWEEN …
       OR forecast_period_end BETWEEN …
       )

, which will return you all rows with either entry_period_end or forecast_period_end within the given period.

Update:

A test script:

CREATE TABLE account (AccountID INT NOT NULL, AccountName VARCHAR(100) NOT NULL);

INSERT
INTO     account
VALUES
(1, 'Test'),
(2, 'Foobar'),
(3, 'Test1'),
(4, 'Foobar1');

CREATE TABLE Entries (id INT NOT NULL, AccountID INT NOT NULL, entry_period_end_date DATETIME, forecast_period_end DATETIME, amount FLOAT NOT NULL);

INSERT
INTO    Entries
VALUES
(1, 1, '2009-12-31', '2009-12-31', 100),
(2, 1, NULL, '2009-10-31', 100),
(3, 2, NULL, NULL, 100),
(4, 3, '2009-10-31', NULL, 100),
(5, 4, '2009-10-31', '2009-10-31', 100);

SELECT  a.*, ef.id
FROM    Account a
LEFT JOIN
        Entries ef
ON      ef.accountID = a.accountID
        AND
        (
        entry_period_end_date BETWEEN '2009-12-01' AND '2009-12-31'
        OR forecast_period_end BETWEEN '2009-12-01' AND '2009-12-31'
        );

returns following:

1, 'Test',    1
2, 'Foobar',  NULL
3, 'Test1',   NULL
4, 'Foobar1'  NULL

Answer 2

Edited to fix logic so end date logic is grouped together, then forecast period logic...

Now it should check for a "good" end date (null or within range), then check for a "good" forecast date (null or within range)

Since all the logic is on the Entries table, narrow it down first, then join

    SELECT a.*,temp.id FROM Account a
LEFT JOIN 
 (
    SELECT id, account_id
    FROM Entries ef
    WHERE
    ((ef.entry_period_end_date BETWEEN $periodStartDate_escaped AND LAST_DAY(date_add( $periodStartDate_escaped, INTERVAL $periodLengthInMonths_escaped MONTH)) 
    OR
    ef.entry_period_end_date IS NULL
    )
AND
    (ef.forecast_period_end BETWEEN $periodStartDate_escaped AND LAST_DAY(date_add( $periodStartDate_escaped, INTERVAL $periodLengthInMonths_escaped MONTH))

    OR 
    ef.forecast_period_end IS NULL
)
 ) temp
ON a.account_id = temp.account_id