Compare two tables in SQL

1

As I am new to SQL please help :

||TABLE 1||||
||COL 1|COL 2|COL 3||
||123|ABC|Submitted||

||TABLE 2||||
||COL 1|COL 2|COL 3|COL 4|
||123|ABC|Completed|A|
||123|ABC|Completed|B|
||123|ABC|Completed|A|
||123|ABC|Completed|B|

Question :

  1. If for COL3 value in Table 2 is Completed for All records ; return the records from column 2
  2. If even for 1 record in COL 3 for Table 2 is not completed; return record from Table 1
2 

DROP TABLE IF EXISTS #table1;
CREATE TABLE #table1 ( col1 int NOT NULL, col2 varchar(30) NULL, 
    col3 varchar(30) NULL );
INSERT INTO #table1 VALUES
    (123, 'ABC', 'Submitted'),
    (456, 'DEF', 'Submitted'); --++

DROP TABLE IF EXISTS #table2;
CREATE TABLE #table2 ( col1 int NOT NULL, col2 varchar(30) NULL, 
    col3 varchar(30) NULL, col4 varchar(10) NULL );
INSERT INTO #table2 VALUES
    (123, 'ABC', 'Completed', 'A'),
    (123, 'ABC', 'Completed', 'B'),
    (123, 'ABC', 'Completed', 'A'),
    (123, 'ABC', 'Completed', 'B'),
    (456, 'DEF', 'Completed', 'A'), --++
    (456, 'DEF', 'Completed', 'B'), --++
    (456, 'DEF', 'In Progress', 'C'); --++


;WITH cte_table2_status AS (
    SELECT col1, CASE WHEN COUNT(*) = SUM(CASE WHEN col3 = 'completed' 
        THEN 1 ELSE 0 END) THEN 1 ELSE 0 END AS are_all_complete
    FROM #table2 
    GROUP BY col1
)
SELECT ca1.*
FROM #table1 t1
INNER JOIN cte_table2_status ct2 ON ct2.col1 = t1.col1
CROSS APPLY (
    SELECT t1.col1, t1.col2, t1.col3, NULL AS col4
    WHERE ct2.are_all_complete = 0
    UNION ALL
    SELECT t2.col1, t2.col2, t2.col3, t2.col4
    FROM #table2 t2
    WHERE t2.col1 = ct2.col1 AND ct2.are_all_complete = 1
) AS ca1
3

Thanks for the reply, however this part of query is not getting executed in "TOAD".

;WITH cte_table2_status AS (
SELECT col1, CASE WHEN COUNT(*) = SUM(CASE WHEN col3 = 'completed'
THEN 1 ELSE 0 END) THEN 1 ELSE 0 END AS are_all_complete
FROM #table2
GROUP BY col1
)

Is there any other way of using a subquery or JOIN ?

4

I have no idea what's valid in TOAD and what isn't, sorry.

5

Are in-line derived tables allowed in TOAD (in whatever dbms you are using)?

Is CROSS APPLY allowed in TOAD?