I have a table TimeIntervals with a relationship to Breaks with in turn has a relationship to DeletedBreaks.
What i'm trying to do is to receive al rows (Breaks) that has timeIntervals id and no deleted break for a given date.
That is if a break has no deleted breaks, no row in DeletedBreaks table for a break id Or if there is a row with that breaks id but not the given date, than that break should be returned.
Following is not working but you might understand what i'm trying to do:
SELECT B.*
FROM Breaks B
JOIN TimeIntervals T
ON B.TimeIntervalId = T.Id
JOIN DeletedBreaks DB
ON (
(
DB.BreakId = B.Id
AND DB.DeletedDate <> '2014-10-13'
)
OR DB.BreakId IS NULL
)
AND (T.Id = 2)
Use a LEFT JOIN to your DeletedBreaks table instead of an inner join since you don't want to drop Break records just becasue the DeletedBreaks ID is null.
To test for NULL DeletedBreaks or DeletedBreaks for a particular day, do so in the WHERE clause:
SELECT B.*
FROM Breaks B
JOIN TimeIntervals T
ON B.TimeIntervalId = T.Id
LEFT JOIN DeletedBreaks DB ON
DB.BreakId = B.Id
WHERE
(DB.DeletedDate <> '2014-10-13'
OR DB.BreakId IS NULL)
AND T.Id = 2
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments