SQL after table JOIN remove extra rows-CodePudding

After joined 2 tables and the results as following. I would like to remove some extra rows. enter image description here

For example: Id a13b has status2 = Error and Success, I need to remove the row where ID = a13b and status2 = Success same as ID a15b and a16c from the table.

This is the result I am looking for and would like to know what's the correct query to get this result. enter image description here

Thank you in advance!

CodePudding user response：

Maybe someone can refer a method to delete. But I think you can "WHERE" clause to solve this problem. Then create a new table with "WHERE" clause.

For example:

SELECT ... FROM 
....
WHERE (ID NOT IN ('a13b', 'a15b', 'a16c') AND (status2 LIKE 'Success'))
AND (ID NOT IN 'a13b' AND Status2 IN ('Error','Success'))

Briefly you can use WHERE and NOT IN clause.

CodePudding user response：

Hi this query should work for the desired result


    select * from dbo.table1 t1 
             inner join dbo.table2 t2 
                on t1.id = t2.Id2 
                    and t1.id not in 
                    (
                        select id2 
            
                        from dbo.table1 t1 
                        inner join dbo.table2 t2 
                            on t1.id = t2.Id2 
                            and status2 ='error'
                    ) 

    union

    select * from dbo.table1 t1 
             inner join dbo.table2 t2 
                on t1.id = t2.Id2 
                    and t2.status2 = 'error';