SQL join query help

[poff]

I have written a forum and am using the following query to search in it:

$query="select topics.tid,f_messages.messid from f_messages left join
topics on f_messages.tid = topics.tid where ($title_list) OR ($msg_list)";

I trimmed it a bit to save space here (the last two are just strings).

It works fine, but it's returning more than once the same topics.tid

I want it to return only unique topic id's.

distinct doesn't work. I think it has to do with the f_messages.tid =
topics.tid as it isn't using distinct there.

I tried returning distinct(f_messages.tid) instead but again, no help!

This is using mysql. Re-structuring the db isn't an option.

Thanks for any light on this one, I can only think of dumping the tid's
into a TEMP dbase and selecting distinct from there, although it sure is
messy!
--
po**@sixbit.org
SDF Public Access UNIX System - http://sdf.lonestar.org

[poff]

In article <be************@ID-30799.news.uni-berlin.de>, Agelmar wrote:

This should produce an output of something like
Topic ID | Message ID
1 |1
1 |2
1 |3
2 |7
2 |13
3 |4
3 |5
...
Right, thing is, on a forum search you don't want to see the same topic a
gazillion times.

I want unique topic id's, which goes against my query.

The (group by topics.tid) part seemed to reduce this, but I'm not getting
unique (distinct) tid's.
unless of course you only care if *some* message in a given thread matches,
and you just want the first message in the thread that matches and don't
care about the rest...
Yeah.
in this case, you can just do something like:

$query="select topics.tid, MIN(f_messages.messid) from f_messages left join
topics on f_messages.tid = topics.tid GROUP BY topics.tid where
($title_list) OR ($msg_list)";

I hope this helps, I must admit I am not totally sure that I understand your
question, but I gave it my best and I hope this is what you're looking for.

Thanks, this actually solved it, where above I have duplicate tid's even
after your addition this is due to proper db duplications which shouldn't
be there.

Thanks!

On a side note I had to put the GROUP BY at the end of the query to get it
to work.

--
po**@sixbit.org
SDF Public Access UNIX System - http://sdf.lonestar.org

[Nikolai Chuvakhin]

poff <po**@sixbit.org> wrote in message
news:<sl*****************@otaku.freeshell.org>...

I have written a forum and am using the following query to search in it:

$query="select topics.tid,f_messages.messid from f_messages left join
topics on f_messages.tid = topics.tid where ($title_list) OR ($msg_list)";

I trimmed it a bit to save space here (the last two are just strings).

It works fine, but it's returning more than once the same topics.tid
And it should... Let's say topic number 5 has three messages in it
(messid = 123, 135, and 148), so if you run

SELECT topics.tid, f_messages.messid
FROM f_messages LEFT JOIN topics
ON f_messages.tid = topics.tid
WHERE topics.tid = 5;

you will get something like this back:

+------------+-------------------+
| topics.tid | f_messages.messid |
+------------+-------------------+
| 5 | 123 |
| 5 | 135 |
| 5 | 148 |
+------------+-------------------+

Remember, DISTINCT applies to an ENTIRE ROW IN THE RESULT SET,
NOT to a single field and NOT to an entire record in the source
table. So the three records in the result set above are NOT
distinct and will all be returned.
I want it to return only unique topic id's.

Then ask for them accordingly:

SELECT DISTINCT tid FROM topics WHERE [your WHERE clause];

Also, it seems to me you are trying to fetch the first (and only
the first) message from each topic; this can be done like this:

SELECT topics.tid AS topic, MIN(f_messages.messid) as message
FROM f_messages LEFT JOIN topics
ON f_messages.tid = topics.tid
WHERE [your WHERE clause]
GROUP BY topics.tid;

Cheers,
NC