Select 语句根据文本框中输入的 ID 从两个表中选择记录

发布于 2024-11-03 16:34:28 字数 908 浏览 1 评论 0原文

我有两个 SQL 表，一个考勤表，其中包含字段 attendanceID、StudentID、ModuleID、Present 和 Date。另一张表是Student表，其中有StudentID字段和Name字段。我想生成一个 SQL 语句，该语句从出勤表中选择出勤 ID、学生 ID、模块 ID、当前和日期，同时还根据在文本框控件中输入的学生 ID 选择学生表中的“姓名”字段。任何人都可以帮助我使用 SQL 来实现这一点，我想我需要一个子查询，但我不知道如何做到这一点，因为我只是 MySQL 的初学者。到目前为止，这是我的代码，它选择了出勤表中的所有字段，但没有根据所选的 StudentID 从学生表中选择姓名。

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:RegisterConnectionString %>" 
                    SelectCommand="SELECT * FROM [Attendance] WHERE ([StudentID] = @StudentID)">
                    <SelectParameters>
                        <asp:ControlParameter ControlID="pnumTextBox" Name="StudentID" 
                            PropertyName="Text" Type="String" />
                    </SelectParameters>
                </asp:SqlDataSource>

提前致谢！

原文

I have two SQL Tables, an Attendance Table which has the fields AttendanceID, StudentID, ModuleID, Present and Date. The other table is Student Table, which has StudentID field and Name field.
I want to generate an SQL statement which selects the AttendanceID, StudentID, ModuleID, Present and Date from the Attendance Table but also selects the Name field in the Student Table depending on the StudentID entered in a Textbox Control.
Could anyone help me with the SQL to achieve this, I think I need a subQuery but I do not know how to do this as I am only a beginner with MySQL.
Here is my code so far which selects all the fields in the Attendance Table but does not select the Name from the Student Table based on the StudentID chosen.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:RegisterConnectionString %>" 
                    SelectCommand="SELECT * FROM [Attendance] WHERE ([StudentID] = @StudentID)">
                    <SelectParameters>
                        <asp:ControlParameter ControlID="pnumTextBox" Name="StudentID" 
                            PropertyName="Text" Type="String" />
                    </SelectParameters>
                </asp:SqlDataSource>

Thanks in advance!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

秋叶绚丽 2024-11-10 16:34:28

您需要连接 Student 和 Attendance 表才能获取该信息。这是一个可以做到这一点的查询。

SELECT AttendanceID, Student.StudentID, ModuleID, Present, Date, Name 
FROM Attendance, Student 
WHERE Attendance.StudentID = Student.StudentID 
AND (Student.StudentID = @StudentID)

You need to join Student and Attendance tables to get that information. Here is a query which would do that.

SELECT AttendanceID, Student.StudentID, ModuleID, Present, Date, Name 
FROM Attendance, Student 
WHERE Attendance.StudentID = Student.StudentID 
AND (Student.StudentID = @StudentID)

回复收藏 0 原文

~没有更多了~