Problem with LEF OUTER JOIN
Posted by:
wdeveloper
Date: March 19, 2006 04:01PM
Hi
I have just started to use MySQL, and am stuck with a query. The situation is that I have 2 tables and have to build a left outer join relation.
SELECT R.ThreadID FROM tblthreadreplies R LEFT JOIN tblthreadvisitsbyuser V ON R.ThreadID=V.Thread WHERE (TIME_TO_SEC(TimeDiff(LastVisit,Date))<0 OR TIME_TO_SEC(TimeDiff(LastVisit,Date)) IS NULL) and threadid=119 AND V.UserID=1
WHERE the structure of the 2 tables are as follows:
tblthreadreplies
ID, ThreadID, Reply, Author, Date, LastUpdated, TotalUpdates, EmailNotification, IsDeleted
tblthreadvisitsbyuser
ID, Thread, Forum, UserID, LastVisit
The main aim is to fetch records from tblthreadreplies irrespective of a corresponding record in tblthreadvisitsbyuser, but when I use the V.UserID=1 in WHERE clause, it stops the getting of records, probably the null-rejection as I read in optimizer.
I would request someone to help me on this and guide me how to get the correct records while meeting all conditions. If I remove the V.UserID=1 in WHERE clause, it would slow the query as well as the complete site for all users as the number of records in the visits table is in 000's. I hope that experts on this site would easily be able to solve this for me in minutes.
Thanks in advance
Regards
Sandy