How to JOIN these 2 tables (using T-SQL)?

Question

I am using SQL Server 2014 and I have these 2 tables to join so as to get the output shown below.

Table 1 (ResStayDate):

 ResID     StayDate       PlanCode    Rateamount
  150      2015-08-07       UKHB        124.00
  150      2015-08-08       UKHB        124.00
  150      2015-08-09       UKHB        124.00
  176      2015-09-10       FRAI         98.00
  176      2015-09-11       FRAI         98.00
  188      2015-10-03       GRHB         29.00
  188      2015-10-04       GRHB         29.00
  188      2015-10-05       GRHB         29.00

Table 2 (ResStay):

 ResID       CurrencyCode     Status
  150           GBP            OK
  176           EUR            CANCELED
  188           EUR            OK

I need the following output:

 ResID     StayDate       PlanCode    Rateamount   CurrencyCode    Status
  150      2015-08-07       UKHB        124.00        GBP            OK
  150      2015-08-08       UKHB        124.00        GBP            OK
  150      2015-08-09       UKHB        124.00        GBP            OK
  188      2015-10-03       GRHB         29.00        EUR            OK
  188      2015-10-04       GRHB         29.00        EUR            OK
  188      2015-10-05       GRHB         29.00        EUR            OK

This how my query looks as at now (but it's not giving the output above):

 Use MyDatabase

 Select a.ResID
       ,a.StayDate
       ,a.PlanCode
       ,a.Rateamount
       ,b.CurrencyCode
       ,b.Status
FROM ResStayDate a
LEFT JOIN ResStay b ON a.ResID = b.ResID
WHERE b.Status <> 'CANCELED'

I can't figure out why it's not giving me the output I'm after. It is giving me the fields (columns) I need but it is duplicating the rows many times. Is it a JOIN syntax issue? I've tried INNER JOIN but that also did not work.

Answer 1

A left join is an outer join which generates a result that contains all the records of the "left" table even when there are no matching records in other tables taking part in the join.

You should be using INNER JOIN. What was wrong with the result when you used INNER JOIN?

Answer 2

You indicated you have used Inner Join and Left Join. Inner Join is correct is it possibly just the misspelling of the word "Cancelled" in your where clause. Here is the creation of your data and query that returns your required output.

create table ResStayDate 
 (ResID smallint,
 StayDate date,
 PlanCode nvarchar(10),
 Rateamount decimal (10,2)
 )

 insert into ResStayDate (ResID,StayDate,PlanCode,Rateamount)
 values ('150','2015-08-07','UKHB','124'),
        ('150','2015-08-08','UKHB','124'),
        ('150','2015-08-09','UKHB','124'),
        ('176','2015-09-10','FRAI','98'),
        ('176','2015-09-11','FRAI','98'),
        ('188','2015-10-03','GRHB','29'),
        ('188','2015-10-04','GRHB','29'),
        ('188','2015-10-05','GRHB','29')

create table ResStay 
 (ResID smallint,
 CurrencyCode nvarchar(10),
 StatusCode nvarchar(10)
 )

insert into ResStay (ResID,CurrencyCode,StatusCode)
 values ('150','GBP','OK'), 
        ('176','EUR','CANCELLED'), 
        ('188','EUR','OK')


Select * 

from ResStayDate as rd

    inner join ResStay as rs
        on rd.ResID=rs.ResID

where rs.StatusCode<>'CANCELLED'

ResID     StayDate       PlanCode    Rateamount   CurrencyCode    Status
  150      2015-08-07       UKHB        124.00        GBP            OK
  150      2015-08-08       UKHB        124.00        GBP            OK
  150      2015-08-09       UKHB        124.00        GBP            OK
  188      2015-10-03       GRHB         29.00        EUR            OK
  188      2015-10-04       GRHB         29.00        EUR            OK
  188      2015-10-05       GRHB         29.00        EUR            OK

Answer 3

You either have more rows than you think, test this with:

Select *
FROM ResStayDate a
WHERE a.ResID = 150

Select *
FROM ResStay b
WHERE b.ResID = 150

OR You have a typo like:

LEFT JOIN ResStay b ON a.ResID = a.ResID

Answer 4

There are duplicate ids in ResStayDate so its matching for every row based on the id. Because you are using a left join (which means give me every row from the table on the left and any matching columns from the right) you will have rows from every row in ResStayDate and it will put nulls for the columns that don't have a corresponding match in ResStay