MySQL Forums
Forum List  »  Optimizer & Parser

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Problem with LEF OUTER JOIN
3276
March 19, 2006 04:01PM
1863
March 19, 2006 05:48PM
2488
March 20, 2006 04:04AM
2263
March 20, 2006 10:31AM
2036
March 20, 2006 03:02PM
1884
March 20, 2006 03:34PM
1960
March 21, 2006 06:35PM
1947
March 22, 2006 05:10PM
2016
March 22, 2006 07:06PM
2121
March 24, 2006 02:01PM
2199
March 26, 2006 08:05PM
2162
March 26, 2006 11:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.