Query join plus criteria question

[djj55]

Hello, SQL2008R2
Okay it is Friday and I just cannot think of how to do this.

What I want is to get the data that joins to a record in a second table and the data that matches criteria into a "newtable".

I would prefer not to use a union command.

I am rewriting a stored procedure that has two INSERT INTO queries.

-- current code
INSERT INTO NewTable (Col1, Col2, Col3, Col4, Col5)
SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table1.Col4, Table1.Col5 
FROM Table1 
INNER JOIN Table2 
ON Table1.Col1 = Table2.Col1
WHERE Table1.Col2 IS NOT NULL 
	AND ISNULL(Table1.Col3, '') = 'Test'

INSERT INTO NewTable (Col1, Col2, Col3, Col4, Col5)
SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table1.Col4, Table1.Col5 
FROM Table1 
WHERE (Table1.Col4 = 'Check1' OR Table1.Col4 = 'Check2') 
	AND Table1.Col5 IS NOT NULL 
	AND ISNULL(Table1.Col3, '') = 'Test'

Is there a way to combine these two queries?

Thank you,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[markros]

;with cte as (SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table1.Col4, Table1.Col5 
FROM Table1 where (exists (select 1 from Table2 where Table1.Col1 = Table2.Col1 and Table2.Col2 IS NOT NULL)
OR (Table1.Col5 IS NOT NULL and Table1.Col4 IN ('Check1','Check2'))
and Table1.Col3 = 'Test' -- why do we need ISNULL?

select * from cte

If the above select will give you correct records then turn it into insert

PluralSight Learning Library

 

[djj55]

Thank you.

Thought about a version of exists (Col1 in (select ...)), but yours is better.

The ISNULL is a bad habit.

Thanks again,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[djj55]

By the way this seems to work

INSERT INTO NewTable (Col1, Col2, Col3, Col4, Col5)
SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table1.Col4, Table1.Col5
FROM Table1
   LEFT JOIN Table2
      ON Table1.Col1 = Table2.Col1
WHERE (Table1.Col2 IS NOT NULL
    OR ((Table1.Col4 = 'Check1' OR Table1.Col4 = 'Check2')
        AND Table1.Col5 IS NOT NULL))
    AND Table1.Col3 = 'Test'

As I needed some values from Table2 in the actual code.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!