CODEWITHSUNDEEP
sqldb2
Zo Has
Zo Has

Reputation: 13038

SQL Query: select all the records from table1 which exist in table2 + all the records from table2 which don't exist in table1

consider the following example. alt text

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 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.

Upvotes: 1

Views: 265

Answers (3)

Jan
Jan

Reputation: 2293

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))

Upvotes: 0

Scott Munro
Scott Munro

Reputation: 13576

You will need an outer join here.

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

Upvotes: 1

Andomar
Andomar

Reputation: 238126

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".

Upvotes: 2

Related Questions