468,247 Members | 1,323 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,247 developers. It's quick & easy.

SQL join query help

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
Jul 16 '05 #1
2 3586
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
Jul 16 '05 #2
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
Jul 16 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Good Man | last post: by
2 posts views Thread by dskillingstad | last post: by
4 posts views Thread by bhargav.desai | last post: by
6 posts views Thread by davegb | last post: by
3 posts views Thread by Zeff | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.