CODEWITHSUNDEEP
sqlsql-server
Haris Mehmood
Haris Mehmood

Reputation: 902

Select using LEFT OUTER JOIN with condition

I have two tables Table A and Table B

Table A

1. *id*
2. *name*

Table B

1. *A.id*
2. *datetime*

I want to select 

1. *A.id*
2. *A.name*
3. *B.datetime*

Even if table B do not contains a row with A.id for specific day and it should replace that column with NULL

e.g

Table A contains 

1. *(1 , Haris)*
2. *(2, Hashsim)*

Table B Contains following for today's date.

1. *(1, '2014-12-26 08:00:00')*

I should show 2 results with id 1 and 2 instead of only id 1.

Using LEFT OUTER JOIN with WHERE Clause makes it a LEFT INNER JOIN, how to work around that ?

Upvotes: 0

Views: 3035

Answers (4)

Pரதீப்
Pரதீப்

Reputation: 93754

Use LEFT OUTER JOIN to get all the rows from Left table and one that does not have match will have NULL values in Right table columns

SELECT A.id,
       A.name,
       B.[datetime]
FROM   tableA A
       LEFT OUTER JOIN tableB B
                    ON A.Id = B.id
                       AND B.[datetime] < @date

Upvotes: 0

Sarath Subramanian
Sarath Subramanian

Reputation: 21381

Select A.id,A.name,B.datetime 
from tableA A 
Left join
(
   SELECT B.ID,B.datetime 
   FROM tableB B
   WHERE B.datetime <= 'myDateTime'
)B
ON A.aid = B.id

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416131

SELECT a.id, a.name, b.datetime
FROM A
LEFT JOIN B on B.aid = a.id
WHERE coalesce(B.datetie, '1900-01-01') < @MyDateTime

Upvotes: 0

SimarjeetSingh Panghlia
SimarjeetSingh Panghlia

Reputation: 2200

SELECT A.id, A.name, b.datetime
FROM A
LEFT Outer JOIN B on B.id = A.id

Upvotes: 1

Related Questions