sql语句 - 两个表之间的关系

I have two tables in access: Messages which has :

我有两个访问表：消息有：

MessageID
邮件ID
MessageFrom
MessageFrom
MessageTO
MessageTO
MessageSubject
信息主题
Messages
消息

User which has

用户有

UserID
用户名
Username
用户名
Password
密码

. Those two tables have a relationship between UserID and MessageTO.

。这两个表在UserID和MessageTO之间有关系。

My user connects and I have to check if it's the right login / password in my database. THEN I need to display informations from both tables. I am not sure how to join them.

我的用户连接，我必须检查它是否是我的数据库中正确的登录名/密码。然后我需要显示两个表的信息。我不知道如何加入他们。

<%  
var mycon;
mycon = new ActiveXObject("ADODB.Connection");
var myrec = new ActiveXObject("ADODB.Recordset"); 
mycon.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Omnivox.mdb"); 

var txtpassword = Request.QueryString("txtpassword");
var txtuserID = parseInt (Request.QueryString("txtuserID"));
  if (txtpassword == "" || txtuserID == "")
{
    Response.Redirect("index.aspx");
}

sql= "SELECT * FROM UserOmnivox WHERE UserID=" +txtuserID+ " AND UserPassword='" + txtpassword + "';";    
var sql;
myrec. Open (sql, mycon);   

%>

3 个解决方案

#1

tb_Message table with sample data

带有样本数据的tb_Message表

+-----------+-------------+-----------+----------------+--------------+
| MessageID | MessageFrom | MessageTo | MessageSubject | Message      |
+-----------+-------------+-----------+----------------+--------------+
| 1         | 1           | 2         | Test Subject   | Test Message |
+-----------+-------------+-----------+----------------+--------------+

tb_User table with sample data

带有样本数据的tb_User表

+--------+----------+----------+
| UserID | UserName | Password |
+--------+----------+----------+
| 1      | User1    | 1234     |
+--------+----------+----------+
| 2      | User2    | 4321     |
+--------+----------+----------+

Considering above data structure, below query may help you to pull the data -

考虑到以上数据结构，下面的查询可以帮助您提取数据 -

SELECT M.MessageID, U1.UserName AS MessageFrom, U2.UserName AS MessageTo, M.MessageSubject, M.Message
FROM (tb_Message M
INNER JOIN tb_Users U1 ON M.MessageFrom = U1.UserID)
INNER JOIN tb_Users U2 ON M.MessageTo = U2.UserID

This will show result as -

这将显示结果为 -

+-----------+-------------+-----------+----------------+--------------+
| MessageID | MessageFrom | MessageTo | MessageSubject | Message      |
+-----------+-------------+-----------+----------------+--------------+
| 1         | User1       | User2     | Test Subject   | Test Message |
+-----------+-------------+-----------+----------------+--------------+

#2

You can uses JOINS, in this case you query could be (It's an example with some of the columns): SELECT U.username, U.password, M.messages FROM User U INNER JOIN Message M ON U.UserID=M.MessageTO

你可以使用JOINS，在这种情况下你可以查询（这是一些列的例子）：SELECT U.username，U.password，M.messages FROM User U INNER JOIN Message M ON U.UserID = M.MessageTO

You have to use alias to reference to table User (U) or Message (M), and add the JOIN on the condition, that means the columns with the relationship.

您必须使用别名来引用表User（U）或Message（M），并在条件上添加JOIN，这意味着具有关系的列。

PD: It's a good practice use words in singular in the table names, not plural.

PD：这是一个很好的做法，在表名中使用单数字，而不是复数。

#3

Your join query would basically be

你的连接查询基本上是

Select M.MessageFrom, M.MessageTO, M.MessageSubject
FROM Messages M
INNER JOIN User U
ON M.MessageTO = U.UserID;

However, there is more to fix here. You should use paramterized queries instead of string concatenation. You are currently vulnerable to SQL injection

但是，还有更多要解决的问题。您应该使用参数化查询而不是字符串连接。您目前很容易受到SQL注入攻击

I am also curious as to why I see <% in your code. This leads me to believe that I am looking at an aspx file instead of an aspx.cs file. This code should be in a file that ends in .cs

我也很好奇为什么我在你的代码中看到<％。这让我相信我正在查看aspx文件而不是aspx.cs文件。此代码应位于以.cs结尾的文件中

Having said that, I do not have a ton of webforms experience, nor do I have many reputation points. My answer may not be exactly what you need, but hopefully the links will be of some assistance.

话虽如此，我没有大量的webforms经验，也没有很多声誉点。我的答案可能并不完全符合您的需求，但希望这些链接可以提供一些帮助。

#1