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!


--
[email]poff@sixbit.org[/email]
SDF Public Access UNIX System - [url]http://sdf.lonestar.org[/url]

[poff]

In article <beqlfq$7to5m$1@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.

--
[email]poff@sixbit.org[/email]
SDF Public Access UNIX System - [url]http://sdf.lonestar.org[/url]

[Nikolai Chuvakhin]

poff <poff@sixbit.org> wrote in message
news:<slrnbh144m.e2s.poff@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