INNER JOIN and FULL OUTER JOIN in one query

[EffakT]

So, basically I am trying to make a forum, and I am wondering how other forum software do this?

On the index page, there are all the categories, with their boards. This, I understand how to do. What I don't know how do is the "Latest Post" for each board.

 

I have this query so far, but it doesn't work.

SELECT topics.*,
Users.Name AS AuthorName
FROM topics
INNER JOIN Users
ON Users.ID = topics.Author
FULL OUTER JOIN Comments
ON Comments.Topic = topics.ID
WHERE topics.Board = 1
ORDER BY Comments.Date, topics.Date DESC
LIMIT 1

Is there a better way of doing this?

 

My table structures:

-- phpMyAdmin SQL Dump
-- version 4.0.4.1
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Feb 23, 2014 at 04:46 AM
-- Server version: 5.5.32
-- PHP Version: 5.4.19

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `forums`
--
CREATE DATABASE IF NOT EXISTS `forums` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `forums`;

-- --------------------------------------------------------

--
-- Table structure for table `board`
--

CREATE TABLE IF NOT EXISTS `board` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(80) NOT NULL,
  `Description` text NOT NULL,
  `Category` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `board`
--

INSERT INTO `board` (`ID`, `Name`, `Description`, `Category`) VALUES
(1, 'First Board', 'Im in Category 1 :3', 1),
(2, 'Board 2', 'I''m the second board :3', 2),
(3, 'Baord3', 'Cat1', 1),
(4, 'Board4', 'Cat2', 2);

-- --------------------------------------------------------

--
-- Table structure for table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(80) CHARACTER SET utf8 NOT NULL,
  `Description` text NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`ID`, `Name`, `Description`) VALUES
(1, 'Category1', 'I am the first category'),
(2, 'Category2', 'I''m the second category :3');

-- --------------------------------------------------------

--
-- Table structure for table `comments`
--

CREATE TABLE IF NOT EXISTS `comments` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Content` text NOT NULL,
  `Date` datetime NOT NULL,
  `Topic` text NOT NULL,
  `Author` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `topics`
--

CREATE TABLE IF NOT EXISTS `topics` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Subject` varchar(80) NOT NULL,
  `Date` datetime NOT NULL,
  `Board` int(11) NOT NULL,
  `Author` int(11) NOT NULL,
  `Content` text NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `topics`
--

INSERT INTO `topics` (`ID`, `Subject`, `Date`, `Board`, `Author`, `Content`) VALUES
(1, 'Topic 1', '2014-02-04 00:00:00', 1, 1, 'CONTENT :3'),
(2, 'New Subject', '2014-02-04 00:00:00', 2, 1, 'Board 2'),
(3, 'New Subject', '2014-02-03 00:00:00', 2, 1, 'Board 2 Thread2');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(80) NOT NULL,
  `Password` varchar(80) NOT NULL,
  `Email` varchar(80) NOT NULL,
  `LastOnline` datetime NOT NULL,
  `RegisteredDate` datetime NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`ID`, `Name`, `Password`, `Email`, `LastOnline`, `RegisteredDate`) VALUES
(1, 'awesomeusername', 'iwillbeencodedeventually', 'awesome@email.com', '2014-02-21 00:00:00', '2014-02-20 00:00:00');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Thanks

--EffakT

[kicken]

Your comments.Topic column needs to be changed to an INT type, not TEXT as you have it now. As for the last post query, what you do is write a query that returns the date of the newest post in each topic using GROUP BY and MAX(). Then you use that as a subquery and join to the comments table to find the details for the comment matching that date.

 

Eg:

SELECT c.ID, c.Content, c.Author
FROM comments c
INNER JOIN (
   SELECT Topic, MAX(Date) as Date
   FROM comments 
   GROUP BY Topic
) mostRecent ON c.Topic=mostRecent.Topic AND c.Date = mostRecent.Date

You'll want a composite index on (comments.Topic, comments.Date) so that the sub query will run quickly.

[EffakT]

sorry, what are 'composite index'? and how do you make them?

[kicken]

A composite index is just an index that covers more that one column. You create them the same as you would any other index, you just list multiple columns rather than one.

 

When the table already exists and you are adding the index, I like to use the CREATE INDEX syntax. If you want to add the index to your CREATE TABLE statement you'd just add it at the end.

CREATE INDEX IX_MostRecent ON comments (Topic, Date)

CREATE TABLE IF NOT EXISTS `comments` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Content` text NOT NULL,
  `Date` datetime NOT NULL,
  `Topic` int NOT NULL,
  `Author` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`),
  INDEX IX_MostRecent (Topic, Date)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

[EffakT]

Also, with the query you posted before, how would i go about getting the topic info and the poster's info? also if there is no comments it needs to just display the topic with the poster's info...

[kicken]

Also, with the query you posted before, how would i go about getting the topic info and the poster's info?

Join with the appropriate tables and fetch their info. Select from the topics table to start then add the joins needed for the rest of the table. Use a LEFT JOIN rather than INNER JOIN when finding the latest post so that you still get the topic info if there is no comment. You can use the COALESCE function for the last post author vs topic author info.

[EffakT]

Join with the appropriate tables and fetch their info. Select from the topics table to start then add the joins needed for the rest of the table. Use a LEFT JOIN rather than INNER JOIN when finding the latest post so that you still get the topic info if there is no comment. You can use the COALESCE function for the last post author vs topic author info.

OK, I setup a query, which to my eyes should work, but then again I'm not great with SQL.

SELECT COALESCE(
	SELECT c.ID AS cID, c.Content AS cContent, c.Author AS cAuthor, topics.Author AS tAuthor, topics.Subject AS tSubject, topics.ID AS tID
	FROM comments c
	INNER JOIN (
		SELECT Topic, MAX(Date) as Date
		FROM comments
		GROUP BY Topic
	) mostRecent ON c.Topic=mostRecent.Topic AND c.Date = mostRecent.Date
	LEFT JOIN topics ON c.Topic=topics.ID
	WHERE topics.Board = 1
	LIMIT 1
),
(
	SELECT topics.*, Users.Name AS AuthorName
	FROM topics
	INNER JOIN Users
	ON Users.ID = topics.Author
	WHERE topics.Board = 1
	ORDER BY topics.Date DESC
	LIMIT 1
)

Any Idea?

[jazzman1]

Could you explain and define entire relationships between these entities?

 

Here's your EER Diagram

 

[EffakT]

Got it working with 2 queries,

The comments one runs, then in php it checks if rowCount() is 0, if so, it runs the other query.