在SQL中联接表 [英] Joining tables in SQL

问题描述

我在使用SQL查询时遇到麻烦.

I am having trouble with a SQL query.

SELECT t.topicname, m. *, ms.avatar
   FROM `messages` m
    INNER JOIN topics t ON m.topicid = t.topicid
    inner join users u on m.author=u.username
    inner join misc ms on u.userid=ms.userid
   ORDER BY postdate DESC LIMIT 5

我要做的是从主题表中获取主题名称，从消息表中获取所有内容，从杂项表中获取头像

what i want to do is get the topicname from the topics table, everything from the messages table and avatar from the misc table

我通过topicid加入主题和消息表 我可以通过messages.author和users.username加入messageages和users表，并加入我在users.userid和misc.userid上加入的misc表

I join the topics and messages table by the topicid I can join the messgages and users table by messages.author and users.username and to join to the misc table i join on users.userid and misc.userid

问题是我没有得到想要的结果

The problem is I am not getting the results I want

此查询适用于已连接的邮件和主题表，但我需要添加杂项表，并且唯一的连接方式是贯穿用户表

This query works on the messages and topics tables joined but i need to add the misc table and the only way to join that is throught the users table

SELECT t.topicname, m. *
   FROM `messages` m
    INNER JOIN topics t ON m.topicid = t.topicid
    ORDER BY postdate DESC LIMIT 5

这是表结构

CREATE TABLE `messages` (
  `messageid` int(6) NOT NULL auto_increment,
  `boardid` int(2) NOT NULL default '0',
  `topicid` int(4) NOT NULL default '0',
  `message` text NOT NULL,
  `author` varchar(255) NOT NULL default '',
  `postdate` datetime default NULL,
  PRIMARY KEY  (`messageid`)
)

CREATE TABLE `misc` (
  `miscid` int(4) NOT NULL auto_increment,
  `userid` int(4) NOT NULL default '0',
  `profpic` varchar(100) NOT NULL default '',
  `avatar` varchar(100) NOT NULL default '',
  `signature` text NOT NULL,
  `alerts` enum('y','n') NOT NULL default 'y',
  PRIMARY KEY  (`miscid`)
)

CREATE TABLE `topics` (
  `topicid` int(4) NOT NULL auto_increment,
  `boardid` int(2) NOT NULL default '0',
  `topicname` varchar(255) NOT NULL default '',
  `author` varchar(255) NOT NULL default '',
  `counter` int(5) NOT NULL default '0',
  `sticky` char(1) NOT NULL default 'n',
  `locked` char(1) NOT NULL default 'n',
  PRIMARY KEY  (`topicid`)
)

CREATE TABLE `users` (
  `userid` int(25) NOT NULL auto_increment,
  `first_name` varchar(25) NOT NULL default '',
  `last_name` varchar(25) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  `username` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`userid`)
)

这是只有2个联接的查询的输出，这就是我希望得到的结果(仅我想拥有一个化身)

This is the output of the query with only 2 joins and this is what i expect to get (only i want to have an avatar with it)

在这里看起来不太漂亮，但这就是数据库给我的

It doesn't llok to pretty here but that is what the database gives me

topicname         messageid       boardid         topicid         message         author      postdate
Mauris Eu Neque Ipsum   36  4   8   Suspendisse nibh risus, porta at cursus sed, tinci...   iTuneStinker    2010-04-08 20:31:39
Aliquam Erat Volutpat   35  5   15  Donec volutpat ligula eu lorem pharetra a adipisci...   AwsomeMoon  2010-04-07 21:58:20
Ut Non Risus Elit   34  2   14  Etiam cursus, erat sed placerat fringilla, risus a...   ScaryHair   2010-04-07 15:35:34
Quisque Rutrum Mattis Sagittis  33  5   9   Etiam in elit sit amet nulla scelerisque ultricies...   ScaryHair   2010-04-07 15:33:46
Where Do I Start Trying To Organise A Festival  32  3   12  Morbi a neque aliquam nisl varius lobortis. Sed ve...   ScaryHair   2010-04-07 13:27:40

这是使用连接所有表的查询的结果

and here is the result using the query that joins all tables

topicname         messageid       boardid         topicid         message         author      postdate        avatar
Forum Rules And Guidelines  2   1   1   FORUM RULES     AdRock  2009-04-11 23:05:18     avatar_17200.jpg
Forum Rules And Guidelines  3   2   2   FORUM RULES.    AdRock  2009-04-11 23:05:18     avatar_17200.jpg
Forum Rules And Guidelines  4   3   3   FORUM RULES     AdRock  2009-04-11 23:05:18     avatar_17200.jpg
Forum Rules And Guidelines  5   4   4   FORUM RULES     AdRock  2009-04-11 23:05:18     avatar_17200.jpg
Forum Rules And Guidelines  6   5   5   FORUM RULES     AdRock  2009-04-11 23:05:18     avatar_17200.jpg

第一个队列获得了消息表的最后5条记录，这就是我想要的，但是我希望能够获得与每条消息的作者相关的化身

The first quert gets the last 5 records of the messages table and that is what i want but i want to be able to get the avatar that relates to the author of each message

推荐答案

似乎您没有为每条消息填写用户和头像.

Seems you don't have users and avatars filled for each message.

尝试将INNER JOINS替换为OUTER JOINS.

SELECT  t.topicname, m. *, ms.avatar
FROM    `messages` m
JOIN    topics t
ON      t.topicid = m.topicid
LEFT JOIN
        users u
ON      u.username = m.author
LEFT JOIN
        misc ms
ON      ms.userid = u.userid
ORDER BY
        m.postdate DESC
LIMIT 5

这篇关于在SQL中联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！