How to retrieve the data from 2 tables by joining them

Question

0.00/5 (No votes)

See more:

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

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 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"

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"

Posted 22-Jun-18 1:47am

Member 13142345

Updated 22-Jun-18 2:36am

Add a Solution

Comments

jaket-cp 22-Jun-18 8:05am

You should be able to do it in SQL - something like this (untested):
select
gr.room_no,
gr.room_status,
gr.housekeeping_status,
case
when gr.roomno is null then ''
else 'R'
end AmIReserved
from room_master rm
left join guestreservation gr
on rm.roomno = gr.roomno
and cast(getdate() as date) = cast(arrivaldate as date)
;

Member 13142345 22-Jun-18 9:36am

Thank You. It worked with modifications.

jaket-cp 26-Jun-18 3:21am

glad to help - but I see that Solution 1 is good, he gets my 5+

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2018-06-22T02:37:00

Solution 1

I'd do that this way:

SQL

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: Never, ever use concatenated string like this:

VB.NET

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

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[^]

Posted 22-Jun-18 2:37am

Maciej Los

Comments

Member 13142345 22-Jun-18 9:37am

thank you for the solutions and for the correction.

Maciej Los 22-Jun-18 10:02am

You're very welcome.

Member 13142345 22-Jun-18 10:06am

another query, what if i want to add a condition to that query to show only the rooms based on condition

Hide Copy Code

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

if i wrote like this it is picking all the rooms omitting the where condition and the results are also many. can you help me

Maciej Los 22-Jun-18 17:55pm

Dear,

I can't access to your database, HD or screen... You have to change above query to your needs...

Member 13142345 23-Jun-18 1:25am

Wrong Question... Thank you....