检查具有特定ID的4个不同表中的列值以及失败的返回通过

Question

Tabel_1:

Id    test_id  testname    passfail
1      1001       test1      pass
2      1001       test2      fail
3      1002       test11     pass
4      1002       test12     pass
5      1003       test21     fail
6      1003       test22     fail

Tabel_2:

Id    test_id  testname    passfail
1      1001       test1      pass
2      1001       test2      pass
3      1001       test3      pass
4      1002       test11     pass
6      1002       test13     fail
7      1002       test13     fail

Tabel_3:

Id    test_id  testname    passfail
4      1002       test11     pass
5      1002       test12     pass
6      1003       test31     fail
7      1003       test32     fail

Tabel_4:

Id    test_id  testname    passfail
1      1001       test1      pass
2      1001       test2      pass
3      1001       test3      pass
4      1003       test11     pass
5      1003       test12     pass

I want to check passfail column in all table(Tabel_1, Tabel_2, Tabel_3, Tabel_4) with specific test_id like 1001 or any test_id. if any of row has fail for passfail column belong to id like 1001 then query will be returned only once fail or no fail found in passfail column belong that specific test_id then query will be returned only once pass from passfail colomn.

Answer 1

You can combine the result of each SELECT by using UNION DISTINCT:

SELECT test_id FROM Table_1 WHERE passfail = 'fail'
UNION DISTINCT
SELECT test_id FROM Table_2 WHERE passfail = 'fail'
UNION DISTINCT
SELECT test_id FROM Table_3 WHERE passfail = 'fail'
UNION DISTINCT
SELECT test_id FROM Table_4 WHERE passfail = 'fail'