从两个表和几行的SQL查询 [英] SQL Query From Two Tables And Several Rows

问题描述

我有2张桌子.

第一个:用户

第二个:消息

我的表格关系:

我的问题是:我需要一个查询，该查询将所有要接收的邮件(参数)以及MessageSenderID和MessageReciverID作为名称(而不是ID)返回给我.

My problem is: I need a query that returns me the all the Messages for reciver (the parameter) and the MessageSenderID and MessageReciverID as name, not as id.

我的查询:

SELECT Message.MessageID, Message.MessageSubject, Message.MessageContent,
       u1.UserFisrtName + '' + u1.UserLastName AS Sender1,
       u2.UserFisrtName + '' + u2.UserLastName AS Reciver1
FROM Users AS u1,
     Users AS u2,
     Messages
WHERE u1.UserID = Message.MessageSenderID
  AND u2.UserID = Message.UserID
  AND u2.UserID = ***userid***

错误是:

没有为一个或多个必需参数提供值.

no value given for one or more required parameters.

推荐答案

SELECT Messages.MessageID, Messages.MessageSubject, Messages.MessageContent,
       s.UserFisrtName & ' ' & s.UserLastName AS Sender,
       r.UserFisrtName & ' ' & r.UserLastName AS Reciver
FROM (Messages
INNER JOIN Users AS s ON s.UserID = Messages.MessageSenderID)
INNER JOIN Users AS r ON r.UserID = Messages.MessageReciverID
WHERE r.UserID = **userid**;

这篇关于从两个表和几行的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！