Some help on merging results from two tables

[Rapture]

Hi everyone!
I have a forum on my site and I need a hint how with a simple query to achieve the desired result.

I have three tables:

forums
- ForumID - int

threads
- ForumID - int
- ThreadID - int
- Sticky - int
- ThreadDateStamp - int

texts
- TextDateStamp - int

Now on the screen where I display the list of threads in a certain forum I want to sort the threads by the following criterias: threads.Sticky, texts.TextDateStamp, threads.ThreadDateStamp.

The best query I could think of was:
SELECT *, texts.TextDateStamp
FROM threads
LEFT JOIN texts ON threads.ThreadID = texts.ThreadID
WHERE ForumID = '$ForumID'
ORDER BY Sticky DESC,texts.TextDateStamp DESC,ThreadDateStamp DESC

However this does not return the desired result since there could be more than one row in texts that corresponds to a row in threads and I get duplicated row. Adding DISTINCT after SELECT did not help. I added GROUP BY ThreadID after WHERE but then the result was not sorted by TextDateStamp as I needed it to be.

Could anyone suggest anything?

MySQL version is 3.23.49

 

[tshot]

2 things not clear
1. there is no field as threadid in texts table threads.ThreadID = texts.ThreadID
2. select * would result all fields for all tbales. in yr case texts as wll as threads
it should be

select threads*, TextDateStamp
FROM threads 
LEFT JOIN texts ON threads.ThreadID = texts.ThreadID
WHERE ForumID = '$ForumID' 
ORDER BY Sticky DESC,texts.TextDateStamp DESC,ThreadDateStamp DESC

secondly if u habe 1-n relation in 'threads' and 'texts' then u will get multiple 'texts' rows for one row in 'threads'




----------------
ur feedback is a very welcome desire