CODEWITHSUNDEEP
sqlinner-join
Henry
Henry

Reputation: 148

SQL inner join not retrieving records

I am attempting to join 2 tables.

Table 1 (resource_log) is a data feed comprising of various columns, date, time, contact number etc. Every time a transaction occurs and new row is inserted into the table.

Table 2 (directory) is a directory which provides a name, team, contact number etc.

Contents in Table 2 are updated every so often, with the previous records retained, and more recent records having a recent record date (rdate).

What I want to do – is display records between 2 times and join the most recent directory information from table 2.

I have written this which pulls back both tables but no results.

I'll use a concrete, but hypothetical, example. 

SELECT      top 20 * 
FROM        resource_log r
INNER JOIN          directory
ON          substring(rl.issi,4,4) = 
                    (
                     SELECT     distinct top 1 d.rdate                           
                     FROM       directory d             
                     WHERE      substring(d.id,2,4) = substring(r.id,4,4)           
                     ORDER BY   d.rdate desc
                      )
WHERE       r.date_and_time between '2016-09-13 11:00:00' and '2016-09-13 11:10:00' and r.id = '123456'

Any suggestions?

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think you want cross apply or outer apply:

SELECT top 20 * 
FROM resource_log r OUTER APPLY
     (SELECT TOP 1 d.rdate                           
      FROM directory d             
      WHERE substring(d.id, 2, 4) = substring(r.id, 4, 4)           
      ORDER BY d.rdate desc
     ) d
WHERE r.date_and_time between '2016-09-13 11:00:00' and '2016-09-13 11:10:00' and
      r.id = '123456';

This will return resource_log records with no matching directory. If you don't want them, then use CROSS APPLY.

Also, there is no need for distinct in the subquery, because you are only selecting one value.

Upvotes: 1

Related Questions