Left join with complex join condition
I have two tables and would like to join them. I want all the records from the accounts table, but only the rows that match the criteria from the right table. If the criteria don't match, I only need an account.
The following steps do 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
)
also forces the output of rows from the record table that are outside the requested period.
Sample 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 request should return (when I set startDate = 12/01/2009, endDate 12/31/2009)
AccountID | id
1 1
2 NULL
3 NULL
4 NULL
thanks martin
source to share
If the value is entry_period_end_date
or forecast_period_end
equal NULL
, the row will be returned even if your other column is NULL
not within the period.
Did you mean 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 entry_period_end
or forecast_period_end
within a given period.
Update:
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 the following:
1, 'Test', 1
2, 'Foobar', NULL
3, 'Test1', NULL
4, 'Foobar1' NULL
source to share
Edited to fix the logic, so the end date logic is grouped together, then the time prediction logic ...
Now he has to check the "good" end date (zero or within the range) and then check the "good" forecast date (zero or within the range)
Since all the logic is in the "Records" table, first narrow it down, 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
source to share