First consider a query that computes which rows are actually relevant from tablethree. With the assumption that with "most recently entered result" you mean "most recent enddate" the following query would gather the appropriate rows:
SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
Now you can build a join to retrieve not only sid, but all of the data of tablethree:
SELECT a.*
FROM tablethree a
INNER JOIN (
SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) b
ON a.sid = b.sid AND a.enddate = b.enddate
This is the result set you actually want to "left join in". You have to insert this into your original query:
SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
ON t1.cid = t2.id
LEFT OUTER JOIN (
SELECT a.*
FROM tablethree a
INNER JOIN (
SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) b
ON a.sid = b.sid AND a.enddate = b.enddate
) AS t3
ON t3.sid = t2.sid
WHERE t1.fieldone = 1
AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate
What should also work is the following:
SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
ON t1.cid = t2.id
LEFT OUTER JOIN tablethree AS t3
ON t3.sid = t2.sid
LEFT OUTER JOIN (
SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) mostrecent
ON t3.sid = mostrecent.sid AND t3.enddate = mostrecent.enddate
WHERE t1.fieldone = 1
AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate
AND mostrecent.enddate IS NULL
This includes both tablethree and the new SELECT as left joins, and sorts out the rows where mostrecent.enddate IS NULL (meaning those rows which are actually not most recent). This should lead to the same result, but MySQL may be able to compute this result a little faster. EXPLAIN on both queries should reveal possible differences in computation.
If I understand your requirements correctly, you want each row in DCString, even when there is no matching row in StringData. The two queries you have now are incorrect:
- The first one performs
DCString RIGHT JOIN StringData - this is backward to what you seem to want (and is one of the reasons most sane people avoid RIGHT JOIN whenever possible).
- The second query performs
StringData LEFT JOIN DCString - this is also backward to what you seem to want. I suggested changing the RIGHT JOIN to a LEFT JOIN, without changing the table order, but you swapped the tables too, for no net change. Why that one is currently not returning could be due to a lot of things, no way for us to speculate. Try and generate an estimated plan and see if you are falling prey to any of these reasons.
Again, based on what I think you want (you'll get better answers if you provide some sample data and desired results):
-- don't use regional, ambiguous formats like dd.mm.yyyy:
-- also you can declare multiple variables in one statement
DECLARE @begin datetime = '20160222',
@end datetime = '20160223',
@dcStringID bigint = 6658;
-- don't litter your code with square brackets except where necessary:
SELECT d.ID, s.[TimeStamp] -- bad choice for a column name
-- always use schema prefix!
FROM dbo.DCString AS d
LEFT OUTER JOIN dbo.StringData AS s
ON d.ID = s.DCStringID
AND s.[TimeStamp] >= @begin
AND s.[TimeStamp] < @end
WHERE d.ID = @dcStringID;
Further reading:
Best Answer
The
WHERE date(attIn) like '2016-07-02%'is converting theLEFTjoin to anINNERjoin. The condition should be moved to theONclause.Also:
LIKEfor dates comparisondate()) before comparing it makes indexes useless. It's better to make the condition sargable.The query corrected:
Tested at SQLfiddle.