CODEWITHSUNDEEP
sqlsql-servert-sqljoinselect
Freakishly
Freakishly

Reputation: 1571

How do I join two tables and only take values from the second table if they exist?

TABLE1 is just 1 column & 28 rows. Each row is a DateTime for the last 28 days.

TABLE2 is 2 columns: DateTime and Value. The number of rows is unpredictable, but the DateTime will always be in the last 28 days.

I want to join these two tables, such that the resulting table has 2 columns, DateTime and Value, and 28 rows, one for each of the last 28 days. The Value column is taken from TABLE2 if present, or NULL or -1 if absent.

What is the best way to do this?

TABLE 1 (28x1)

DATE
11/3/2018 12:00:00AM
11/4/2018 12:00:00AM
11/5/2018 12:00:00AM
...
...
11/30/2018 12:00:00AM

TABLE 2 (4x2)

DATE                  | VALUE
----------------------+-------
11/3/2018 12:00:00AM  |  45
11/21/2018 12:00:00AM |  78
11/22/2018 12:00:00AM |  85
11/29/2018 12:00:00AM | 103

Expected result (28x2)

DATE                 | VALUE
---------------------+-------
11/3/2018 12:00:00AM | 45
11/4/2018 12:00:00AM | null
11/5/2018 12:00:00AM | null
...
...
11/20/2018 12:00:00AM | null
11/21/2018 12:00:00AM | 78
11/22/2018 12:00:00AM | 85
11/23/2018 12:00:00AM | null
...
11/29/2018 12:00:00AM | 103
11/30/2018 12:00:00AM | null

Any JOIN I've tried so far doesn't work because I must join on a column, and the only column in common is DateTime & that eliminates the rows with no values.

Upvotes: 1

Views: 826

Answers (3)

Moinul Islam
Moinul Islam

Reputation: 469

Hope this will solve your problem.

SELECT    t1.[datetime], t2.[value]
FROM      t1
LEFT JOIN t2 ON cast(t1.[datetime] as date) = cast(t2.[datetime] as date)

Upvotes: 0

Ajay2707
Ajay2707

Reputation: 5798

My suggestion about the column name, do not used reserved word of sqlserver in column like Date or Datetime.

Given below example that will solved your issue: (As suggested above answer you need left join)

declare @table1 table( dateColumn datetime)
declare @table2 table( dateColumn datetime, value int)

--insert into @table1 values ('11/3/2018 12:00:00AM' ),( '11/4/2018 12:00:00AM'), ('11/5/2018 12:00:00AM')
--To test I insert current month's all dates in @table1
Declare @year int = 2018, @month int = 11;
WITH numbers
as
(
    Select 1 as value
    UNion ALL
    Select value + 1 from numbers
    where value + 1 <= Day(EOMONTH(datefromparts(@year,@month,1)))
)

Insert into @Table1  
SELECT datefromparts(@year,@month,numbers.value) Datum FROM numbers

insert into @table2 values ('11/3/2018 12:00:00AM' , 45), ('11/21/2018 12:00:00AM', 78), ('11/22/2018 12:00:00AM', 85), ('11/29/2018 12:00:00AM', 103)

SELECT    t1.dateColumn, t2.[value]
FROM    @table1  t1
LEFT JOIN @table2 t2 ON t1.dateColumn = t2.dateColumn

Upvotes: 2

Mureinik
Mureinik

Reputation: 310993

This is what left joins were invented for:

SELECT    t1.[datetime], t2.[value]
FROM      t1
LEFT JOIN t2 ON t1.[datetime] = t2.[datetime]

Upvotes: 1

Related Questions