Re: Problem with LEF OUTER JOIN

[wdeveloper]

Just take this example of my situation:

tblCat
- ID
- Name
- Desc

tblCatDet
- ID
- CatID
- Color

Now tblCat has the following data:
1 Test Testdesc
2 Test Testdesc
3 Test Testdesc

tblCatDet has the following data:
1 1 clr
2 1 clr
3 3 clr


Now the query :

select * from tblCat C LEFT OUTER JOIN tblCatDet D ON C.ID=D.CatID WHERE D.color='2'

For the exact tble structure and query look at this:
tblthreads:
ID,
AuthorID,
Email,
Subject,
Body,
CreateDate,
LastUpdated,
TotalUpdates,
IsDeleted

tblthreadvisitsbyusers:
ID,
Thread,
UserID,
LastVisit

tblusers
ID,
Name


SELECT DISTINCT T.ID FROM tblthreads T LEFT OUTER JOIN tblthreadvisitsbyuser V ON T.ID=V.Thread WHERE (TIME_TO_SEC(TimeDiff(LastVisit,Createdate))<0 OR TIME_TO_SEC(TimeDiff(LastVisit,Createdate)) IS NULL) AND V.UserID=1
UNION ALL
SELECT DISTINCT R.ThreadID FROM tblthreadreplies R LEFT OUTER 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 V.UserID=1

Thanks for all the help till now, hope this makes the problem more clear and we can have a solution soon. Or else may be I am trying to achieve somethg wrong.

Sandy