如何通过加入来检索2个表中的数据 [英] How to retrieve the data from 2 tables by joining them

问题描述

我有2张桌子



表1是我存储房间状态，房间号码和内务状态的主表。

I have 2 tables

Table 1 is the master table where i am storing the Room Status, Room Number and housekeeping status.

select room_no,room_status,housekeeping_status from room_master

运行以上查询我将得到以下结果。

By running the above query i will get the the below result.

room_no      room_status       housekeeping_status
801          vacant             Ready
802          Vacant             Dirty
803          Occupied           Dirty
804          vacant             Ready
805          Occupied           Clean

通过使用上述结果我写作以下代码将房间分配到列表中

根据状态添加图像，文本和房间否

By using the above results i am writing the below code to assign the rooms to a list
adding the image,text and room no based on the status

strsql = "select room_no,room_status,housekeeping_status from room_master"
 dt1 = ConnectionModule.HMSgetdataset(strsql)

 Dim lst1() As ListViewItem
        ReDim Preserve lst1(dt1.Rows.Count - 1)
        For i = 0 To dt1.Rows.Count - 1
            If dt1.Rows(i)(1) = "Vacant" And dt1.Rows(i)(2) = "Ready" Then
                lst1(i) = New ListViewItem
                lst1(i).Text = dt1.Rows(i)(0)
                lst1(i).ImageIndex = 2
                lst1(i).BackColor = Color.SpringGreen
                lbl_vacantready.Text = CDbl(lbl_vacantready.Text) + 1
            ElseIf dt1.Rows(i)(1) = "Vacant" And dt1.Rows(i)(2) <> "Ready" And dt1.Rows(i)(2) <> "OutInv" And dt1.Rows(i)(2) <> "OutOdr" Then
                lst1(i) = New ListViewItem
                lst1(i).Text = dt1.Rows(i)(0)
                lst1(i).ImageIndex = 0
                lst1(i).BackColor = Color.CadetBlue
                lbl_vacantnotready.Text = CDbl(lbl_vacantnotready.Text) + 1
            ElseIf dt1.Rows(i)(1) = "Occupied" Then
                lst1(i) = New ListViewItem
                lst1(i).Text = dt1.Rows(i)(0)
                lst1(i).ImageIndex = 1
                lst1(i).BackColor = Color.Aqua
                lbl_occupied.Text = CDbl(lbl_occupied.Text) + 1
            ElseIf dt1.Rows(i)(2) = "OutInv" Then
                lst1(i) = New ListViewItem
                lst1(i).Text = dt1.Rows(i)(0)
                lst1(i).ImageIndex = 4
                lst1(i).BackColor = Color.OrangeRed
                lbl_OOI.Text = CDbl(lbl_OOI.Text) + 1
            ElseIf dt1.Rows(i)(2) = "OutOdr" Then

                lst1(i) = New ListViewItem
                lst1(i).Text = dt1.Rows(i)(0)
                lst1(i).ImageIndex = 5
                lst1(i).BackColor = Color.Orange
                lbl_OOR.Text = CDbl(lbl_OOR.Text) + 1
            End If
        Next

我尝试了什么：



现在要求是在任何房间预订时我需要附加

（R）在房间旁边没有。

如需查看预订，我使用以下查询。

What I have tried:

Now the requirement is when there is a reservation with any room then i need to append
"(R)" beside the room no.
For checking the reservation i am using the below query.

strsql="select roomno from guestreservation where arrivaldate=cast('" & Format(currdate(), "dd-MMM-yyyy") & "' as smalldatetime)"

它会将预订的房间号码作为今天的日期。

it will give the room numbers which has the reservation as today date.

room_no
801
802

so对于房间号码801和802，需要在房间号旁边附加（R）。

i已写下面的行以附加（R）。但是只有在预订房间时才必须消失，对于剩余的房间，房间号码将显示为相同。

so for room numbers 801 and 802 need to append the "(R)" beside the room number.
i have written the below line to append the "(R)". But it has to be disaplyed only when there is reservation with that room, for the remaining rooms the room number will be displayed as the same.

lst1(i).Text = dt1.Rows(i)(0).Tostring() + "R"

所以我假设追加（R ）只在循环中。

so i am assuming to append the "(R)" in the loop only.

"how can i join the 2 queries to get the room number as reserved"

推荐答案

我这样做：

I'd do that this way:

SELECT RM.room_no, RM.room_status, RM.housekeeping_status, CASE WHEN GR.roomno IS NULL THEN 'F' ELSE 'R' END AS FreeOrReserved
FROM room_master AS RM LEFT JOIN 
(
    SELECT *
    FROM guestreservation 
    WHERE arrivaldate = CAST(GETDATE() AS SMALLDATETIME)
) AS GR ON RM.room_no = GR.roomno

BTW：从来没有像这样使用串联字符串：

BTW: Never, ever use concatenated string like this:

strsql="select roomno from guestreservation where arrivaldate=cast('" & Format(currdate(), "dd-MMM-yyyy") & "' as smalldatetime)"

因为您为数据库公开 SQL Injection [ ^ ]。

更好地使用参数化查询 [ ^ ]（在此例如 - 你不需要）。



详情请见：

SQL连接的可视化表示 [ ^ ]

如何：执行参数化查询| Microsoft Docs [ ^ ]

because you expose you database for SQL Injection[^].
Better use parameterized queries[^] (which in this example - you don't need).

For further details, please see:
Visual Representation of SQL Joins[^]
How to: Perform Parameterized Queries | Microsoft Docs[^]

这篇关于如何通过加入来检索2个表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！