如何使用相同的catID从一个表获得一个结果，从另一个表获得多个结果？

Question

我想建立一个我不知道的论坛。只是在玩代码。

现在我得到了两张表格，第一张有论坛类别，第二张有论坛板。

我现在的问题是，我是否可以使用左联接或任何连接从类别表中获取每个连接中的一个，以及从主板上获得相同catID的所有结果？

这是我现在没有工作的代码：

$query = $_database->query("
SELECT boards.name AS boardName, boards.info, boards.category, boards.boardID, boards.sort, boards.topics, boards.posts,
categories.catID, categories.name AS catName, categories.sort 
FROM ".PREFIX."forum_boards AS boards LEFT JOIN ".PREFIX."forum_categories AS categories ON boards.category = categories.catID GROUP BY categories.catID ORDER BY boards.boardID");
while ($rad = $query->fetch_array()) {
    echo '<h2>'.$rad['catName'].'</h2>';
    echo $rad['boardName'];
} 

有人要求张贴我的表结构，这是

董事会：

CREATE TABLE IF NOT EXISTS `mm_forum_boards` (
`boardID` int(11) NOT NULL,
  `category` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `info` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `sort` int(2) NOT NULL DEFAULT '0',
  `topics` int(11) NOT NULL DEFAULT '0',
  `posts` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

类别：

CREATE TABLE IF NOT EXISTS `mm_forum_categories` (
`catID` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `info` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `sort` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

一些数据：

INSERT INTO mm_forum_categories (`name`, `info`, `sort`) VALUES('Test Cat 1', 'Just giving some lorem data ^^', '1');
INSERT INTO mm_forum_categories (`name`, `info`, `sort`) VALUES('Test Cat 2', 'Just giving some lorem data ^^', '2');
INSERT INTO mm_forum_boards (`category`, `name`, `info`, sort`) VALUES('3', 'Test board 1', 'Lorem board for now', '1');
INSERT INTO mm_forum_boards (`category`, `name`, `info`, sort`) VALUES('3', 'Test board 2', 'Lorem board for now', '2');
INSERT INTO mm_forum_boards (`category`, `name`, `info`, sort`) VALUES('3', 'Test board 3', 'Lorem board for now', '3');

INSERT INTO mm_forum_boards (`category`, `name`, `info`, sort`) VALUES('4', 'Test board 4', 'Lorem board for now', '1')

Answer 1

SQL中的GROUP BY子句在这里是错误的。它使用相同的category_id聚集所有行。这是有用的，当你想数他们，但不是在这里。

当您想按类别订购时，将其放在ORDER BY子句中

Answer 2

按照下面的查询

SELECT
  mm_forum_categories.name,
  mm_forum_categories.info,
  mm_forum_boards.name,
  mm_forum_boards.info,
  mm_forum_boards.sort,
  mm_forum_boards.topics
FROM
  mm_forum_categories
LEFT Join mm_forum_boards 
  ON mm_forum_categories.catID = mm_forum_boards.category
WHERE
  mm_forum_categories.catID =  '1'

我想它能解决你的问题。

Answer 3

据我所知，您需要一个简单的RIGHT OUTER JOIN。看看这个查询。

SELECT mfc.name AS cat_name, mfc.catID as catID
FROM mm_forum_categories mfc
RIGHT OUTER JOIN mm_forum_boards 
ON mfc.catID = mm_forum_boards.category;