Cant figure out how to join these tables

Question

Need help with some kind of join here. Cant figure it out.

I want to loop out the forum boards, but I also want to get last_post and last_posterid from posts, and based on last_posterid get username from users.

This is how far I've come yet (:P):

SELECT name, desc, position FROM boards b
INNER JOIN posts p ON ???
INNER JOIN users u ON ???
ORDER BY b.position ASC

Help would be greatly appreciated.

Cheers

CREATE TABLE `posts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`posterid` int(10) unsigned NOT NULL DEFAULT '0',
`subject` varchar(255) DEFAULT NULL,
`message` text,
`posted` int(10) unsigned NOT NULL DEFAULT '0',
`edited` int(10) unsigned DEFAULT NULL,
`edited_by` int(10) unsigned NOT NULL DEFAULT '0',
`icon` varchar(255) DEFAULT NULL,
`topicid` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

CREATE TABLE `boards` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(200) NOT NULL,
`desc` varchar(255) NOT NULL,
`position` int(10) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

CREATE TABLE `users` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(200) NOT NULL,
`password` varchar(255) NOT NULL,
`salt` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

CREATE TABLE `topics` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`posterid` int(10) unsigned NOT NULL DEFAULT '0',
`subject` varchar(255) NOT NULL,
`posted` int(10) unsigned NOT NULL DEFAULT '0',
`last_post` int(10) unsigned NOT NULL DEFAULT '0',
`last_poster` int(10) unsigned NOT NULL DEFAULT '0',
`num_views` mediumint(8) unsigned NOT NULL DEFAULT '0',
`num_replies` mediumint(8) unsigned NOT NULL DEFAULT '0',
`closed` tinyint(1) NOT NULL DEFAULT '0',
`sticky` tinyint(1) NOT NULL DEFAULT '0',
`icon` varchar(40) DEFAULT NULL,
`boardid` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

Answer 1

Nothing stands out, so this is mostly a guess:

SELECT b.name, b.desc, b.position
FROM boards AS b
INNER JOIN posts AS p
  ON b.id = p.topicid
INNER JOIN users AS u
  ON p.posterid = u.id
ORDER BY b.position ASC

Answer 2

Ok, lack of a schema not withstanding, it looks like you could use nested select. Something like:

DECLARE @posts TABLE
(
    postdate datetime,
    postid int,
    boardid int
)

DECLARE @users TABLE
(
    userid int,
    username varchar(10)
)

DECLARE @boards TABLE
(
    boardid int
)


INSERT INTO @boards(boardid) VALUES (1)

INSERT INTO @users (userid, username) values (1, 'Adam')

insert into @posts (postdate, postid, boardid) values (GETDATE(), 1, 1)

SELECT b.*, p.* 
from @boards b
inner join
(
    SELECT TOP 1 postdate, postid, boardid, username FROM @posts 
    inner join @users on userid = postid WHERE boardid = 1
    order by postdate desc
) as p on p.boardid = b.boardid

Oviously this is heavily generalised and uses some hard-coded values, but hopefully it should give you an option for implementing it on your own tables.

Of course, I infer no correctness in my implementation of the spec - SQL is not my first language lol

Answer 3

SELECT username, message, posts.id
FROM users, posts
WHERE posterid=users.id
GROUP BY username, message, posts.id
HAVING posted=MAX(posted)

This might just do to get each user's last post concisely.

Answer 4

Assuming posts.topicid refers to boards.id, and posts.posterid refers to users.id, try something like

SELECT b.name, d.desc, b.position
FROM boards b
LEFT JOIN posts p
  ON b.id = p.topicid
LEFT JOIN users u
  ON (p.posterid = u.id) AND (p.posted = (SELECT MAX(sp.posted) FROM posts sp GROUP BY))
ORDER BY
  b.position ASC

Another remark: try to name your fields such that it is clear what they refer to, for instance:

CREATE TABLE `foo` (
  foo_ID UNSIGNED INTEGER NOT NULL AUTO_INCREMENT,
  -- more stuff
  PRIMARY KEY (`foo_ID`)
);

This allows you to re-use the field name foo_ID in another table, which makes things very easy when performing a join.

Answer 5

Was going to post this as a comment but it was too verbose and messy.

1. you'll want to use OUTER JOINs because otherwise boards with no posts in them will not show up
2. any single query that links those four tables is going to be a beast - consider using VIEWs to simplify things
3. your initial question and the tables you posted leave things a bit ambiguous: do you already have last_post and last_poster filled out in topics? If so, then your primary targets for the join should be boards and topics, and since you're already storing redundant data (I assume for speed?), why don't you also put in a last_post_time or something, which would simplify things a lot and curb load (no need to join in posts at all)?
4. mediumints? why on Earth? (If you don't need the date, and you think it'll never wrap, I suppose you can use the hack of trusting that a later post will have a higher ID due to autoincrementing key...)