I have three tables. Simplified, they look like this.
1. galleryData(fileID INT)
2. galleryViews(fileID INT, time DATETIME)
3. galleryFeedback(fileID INT, feedback TEXT, time DATETIME)

Now over to my problem. What I want to do is select all rows in galleryData that have a fileID that matches the fileID in galleryFeedback. Meaning, select all fileID's that have a feedback associated with it.

Now that's no problem.

But, I only want the feedback written AFTER the time the file was last viewed. There can be multible rows in galleryViews for each file (since a new row is inserted into the table every time the file is viewed). Hence, if galleryViews.time < galleryFeedback.time, then select the row.

Now, I'm no expert on LEFT JOIN, but this is what i made (that doesn't work):
SELECT galleryData.fileID, galleryFeedback.feedback FROM galleryData LEFT JOIN (galleryFeedback, galleryViews) ON (galleryViews.fileID = galleryViews.fileID AND galleryViews.time < galleryFeedback.time)

All I get from that is null in the feedback column.

What I want to do ultimately is to put a COUNT on the SELECT to see how many feedbacks the user has not seen.

If someone could help me with this I would be delighted, I have worked on this problem for 4 days straight now <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>