Most efficient way to do this cross-table query?

J_Fox

I'm a little confused as to the best way to go about doing this. I have something that works fine, but it's overly redundant and I'm sure it can be made more efficient.

I have this query:

SELECT * FROM `boards_posts` WHERE `author`='$gid' GROUP BY `tid`

Which is simple enough, BUT, I would like to sort the results of that query depending on a time field on a different table. Basically, boards_topics has the last post time for the topic and I'd like to show the most recently created/bumped topics first, not neccessarily in the order they were made.

THanks.

NogDog

For a specific answer, we'd need to know more about the database structure, in particular what column(s) establish the relationship between the tables. The general answer is that you'll probably want to do an "inner join", including the post time column that you want to sort on. For more info, see: http://dev.mysql.com/doc/refman/5.0/en/join.html (assuming we're talking about MySQL here).

J_Fox

The relational columns are named 'tid' and 'bid', topic and board ID.

And after looking at the results of the above query, it seems like that isn't working correctly either.

What I want to do is find out which topics on the boards a user has posted in, and then display only those topics, in order of the last post.

The two main problems I am having are:
- Only displaying each topic once. As it is now, if the user has posted 2+ times in a topic, it will show up on the posted messages list 2+ times.
- Sorting by the last post in the topic.

The posts table also has a timestamp field, so it may or may not be a requisite to join the topic table; I'm not sure of how I would go about doing it.

Basically, my tables (or at least the relevant parts) look something like this:

boards_topics
tid [topic id]
bid [board id]
lastpost [last post time in the topic]

boards_posts
tid [topic id]
posttime [time of the post]

Thanks for the help and let me know if there is any other information I am omitting.

NogDog

SELECT `bp`.`author`, `bp`.`tid`, `bt`.`lastpost`
  FROM `boards_posts` AS `bp` INNER JOIN `boards_topics` AS `bt`
  WHERE `bp`.`tid` = `bt`.`tid` AND `author`='$gid' 
  GROUP BY `bp`.`tid`

If I understand correctly, I think that's what you want.

tomhath

That might work in MySQL, but there are a couple of things that bother me about it (and it won't work in most other DBMS's like SQLServer).

First, the GROUP BY should contain every column that's selected but not in an aggregate, so it should be:

GROUP BY bp.author, bp.tid, bt.lastpost

But there aren't any aggregate functions (COUNT, SUM, etc), so what is being grouped? I think you used GROUP when you meant ORDER.

Second (and more subtle) is how the JOIN is defined. When you join tables, you should specify the join criteria in an ON clause. Then use the WHERE clause to specify which rows from the join you want in the final result set. With an inner join it doesn't matter, but it makes a big difference when you use outer joins.

Maybe something like this (untested):

SELECT bp.author, bp.tid, bt.lastpost
FROM boards_posts AS bp 
INNER JOIN boards_topics AS bt
    ON bp.tid = bt.tid
WHERE author=$gid
ORDER BY bt.lastpost