CODEWITHSUNDEEP
sqlsql-serversql-server-2008
peterbonar
peterbonar

Reputation: 599

Returning results that are present in one table but not in another

I have a query for which I need to return all instances of table A, matching to a specific result set from table B, but even in the event that there is no match from table B, I still want a result from table A but with null values for the table B results. For example:

Table A
Person1 | Date1 |  Info1
Person2 | Date2 |  Info2 
Person4 | Date4 |  Info4

Table B
Person2 | Details2
Person3 | Details3
Person5 | Details5

Desired result
Person1 | Date1 | Info1 | NULL
Person2 | Date2 | Info2 | Details2
Person4 | Date4 | Info4 | NULL

Currently, my report does most of what I need, but it fails to bring back any of the instances such as the Person1 and Person4. It doesn't bring back instances where no matching results are in table 2. I assume this is because of the where Clause. I've tried using a LEFT JOIN, but obviously this didn't work. I've also attempted it using a UNION but to no avail.

Any help would be appreciated!

SELECT t1.[Person1]
  , t1.[forename] 
  , t1.[surname] 
  ,CONVERT(VARCHAR(10), t1.[aDate], 103) + ' '  + convert(VARCHAR(8), t1.[aDate], 14) Admit
  ,CONVERT(VARCHAR(10), t1.[disDate], 103) + ' '  + convert(VARCHAR(8), t1.[disDate], 14) Discharge
  , t1.[wa] 
  , t1.[sp] 
  , t2.title 
  ,CONVERT(VARCHAR(10), t2.DocDate, 103) + ' '  + convert(VARCHAR(8), t2.DocDate, 14) DocDate
FROM [DB1].[dbo].[table1] t1
JOIN [DB2].[dbo].[table2] t2
ON t1. Person1 = t2. Person2
WHERE CAST(t2.DocDate AS DATE) BETWEEN CAST(t1.aDate AS DATE) and 
CAST(t1.disDate AS DATE)
AND t2.title like '%ischarg%'
ORDER BY t1.Person1 ASC, t1.aDate DESC

Upvotes: 1

Views: 58

Answers (2)

SE1986
SE1986

Reputation: 2740

Change JOIN [DB2].[dbo].[table2] t2 to LEFT JOIN [DB2].[dbo].[table2] t2

LEFT JOIN returns all from the left table (table1 in your case) regardless of whether there is a match in the right table (table 2)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269453

You want a LEFT JOIN. You have to be careful about the filtering conditions:

SELECT . . .
FROM [DB1].[dbo].[table1] t1 LEFT JOIN
     [DB2].[dbo].[table2] t2
      ON t1. Person1 = t2. Person2 AND
         CAST(t2.DocDate AS DATE) BETWEEN CAST(t1.aDate AS DATE) and 
CAST(t1.disDate AS DATE) AND
         t2.title like '%ischarg%'
ORDER BY t1.Person1 ASC, t1.aDate DESC;

Any conditions on the second table need to go into the ON conditions. Conditions only on the first table can go in a WHERE clause.

Upvotes: 1

Related Questions