Help with SQL Query

Question

Hello guys, consider the following example.

I have to select all the records from table1 which exist in table2 also + all the records from table2 which don't exist in table1 but exist in table2 and have IsActive=1 and status not null.

I initially tried it with a join but how to do the later part where I have to select the records which don't exist in table 1 ? I have to do it inside a single query presumably with a SQL view.

Edit Guys I need to combine the results like a UNION of 2 tables, so incase of rows absent in table1 but present in table2, the columns of belonging to table1 would be blank.

Answer 1

Here's an example query:

select  *
from    Table2 t2
left join
        Table1 t1
on      t1.id = t2.id
where   t1.id is not null
        or (isActive = 1 and status is not null)

The first line of the where clause takes care of "all the records from table1 which exist in table2". The second line is for "don't exist in table1 but exist in table2 and have IsActive=1 and status not null".

Answer 2

You will need an outer join here.

http://msdn.microsoft.com/en-us/library/ms187518.aspx

Answer 3

Is this it? Not sure if I got right what you want to do.

SELECT
    *
FROM
    Table1 t1
        JOIN
    Table2 t2 ON (t1.ID = t2.ID OR (t1.ID IS NULL AND t2.isActive = 1 AND t2.Status IS NOT NULL))

Answer 4

it seems like outer join with where is becoming an inner join.