0

There are three tables T1, T2 and T3.

I want an sql query to return a result with these conditions:

T1.Id is in T2.T1Id (T1.Id = T2.T1Id) 

AND there is not a row in T3 with Id from T2 for a certain date in a field in T3

Update

Following example is first table name -> Columnname -> example data

enter image description here

Input is a date

Return a row if there is a Break with TimeIntervalId and not a DeletedBreak with the inputed date.

do not return a row if there is no break with IntervalId

asdfasdf
  • 211
  • 2
  • 4
  • 9
  • 1
    Please show some sample input and the desired result. Also, what RDBMS are you using? MySQL, SQL-Server, Oracle? – Barmar Nov 14 '14 at 20:12
  • Is the first condition an exact equality comparison between `T1.Id` and `T2.T1Id`, or a substring? – Barmar Nov 14 '14 at 20:13
  • To find rows with no match in another table, use `LEFT JOIN` followed by a `NULL` check, or `NOT IN (SELECT ...)` or `NOT EXISTS (SELECT ...)`. See http://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exists-mysql?lq=1 – Barmar Nov 14 '14 at 20:14
  • #Barmar it's exact, an int comparsion – asdfasdf Nov 14 '14 at 20:16
  • 1
    Please put more effort into making your questions easily comprehensible to potential answer-ers. Provide Table definitions, example data and desired results. – Martin Smith Nov 14 '14 at 20:18
  • 2
    Tried answering but realized it's just going to be query vomit if I don't know what to display. – Jake Nov 14 '14 at 20:29
  • Your question has 36 times be viewed by now. Since you did not add additional information it is diffuclt to tell if you already solved your question or if you are away from keyboard. Others would have helped if you had replied quickly. But without knowing some may consider it a waste of time to wait for you. – surfmuggle Nov 14 '14 at 20:45
  • Sorry about this. I have now updated the question, it is not yet solved – asdfasdf Nov 14 '14 at 20:54

1 Answers1

0

I think what you are looking for is:

DECLARE @Date datetime = CAST('2014-11-13' as datetime)

SELECT 1 Id
into #TimeIntervals

SELECT 1 Id, 1 TimeIntervalId
INTO #Breaks

SELECT 1 Id, 1 BreakId, CAST('2014-11-14' as datetime) as Date
into #DeletedBreaks

SELECT *
FROM #TimeIntervals ti
INNER JOIN #Breaks b on ti.Id = b.TimeIntervalId
LEFT JOIN #DeletedBreaks db on db.BreakId = b.Id AND Date = @Date
WHERE (db.Id is null)

DROP TABLE #TimeIntervals
DROP TABLE #Breaks
DROP TABLE #DeletedBreaks

The inner is important because it ensures that no rows are returned if there are no breaks in the breaks table.

EDIT: b.Id will always be not null, so it was removed from the where clause.

Michael
  • 1,556
  • 13
  • 25