Hey all, thanks for all your support, first time using a forum. i think i lost track trying to simplify the query, here more information about the tables and the query i'm trying to write. If you need for information please feel free to ask.
Table A
ID Name Location
A1 John London
A2 Mike London
A3 Adam London
A4 Steve London
A5 James London
Table B
ID TEST Results
A1 TEST1 SATISFACTORY
A1 TEST2 UNSATISFACTORY
A1 TEST3 SATISFACTORY
A2 TEST1 SATISFACTORY
A2 TEST2 UNSATISFACTORY
A2 TEST3 SATISFACTORY
A3 TEST1 SATISFACTORY
A3 TEST2 SATISFACTORY
A4 TEST1 UNSATISFACTORY
A4 TEST2 UNSATISFACTORY
A5 TEST0 NOT TESTED
at the moment my query is;
Select Distinct TableAID, TableAName, TableALoacation, TableBID, TableBResults
Left outer join TableA on TableID = TableID
So when i run this Query i get the following results
ID NAME ID Results
A1 JOHN A1 SATISFACTORY
A1 JOHN A1 UNSATISFACTORY
A2 MIKE A2 SATISFACTORY
A2 MIKE A2 UNSATISFACTORY
A3 ADAM A3 SATISFACTORY
A4 STEVE A4 UNSATISFACTORY
A5 JAMES A5 NOT TESTED
So if all the tests for a paticular ID is SATISFACTORY(A3) then i get 1 output
'A3 ADAM A3 SATISFACTORY'
So if all the tests for a paticular ID is UNSATISFACTORY(A4) then i get 1 output
A4 STEVE A4 UNSATISFACTORY
Also if all the tests for a paticular ID is NOT TESTED(A5) then i get 1 output
A5 STEVE A5 NOT TESTED
Though if i have an ID with some SATISFACTORY & UNSATISFACTORY then it returns 2 rows
A1 JOHN A1 SATISFACTORY
A1 JOHN A1 UNSATISFACTORY
A2 MIKE A2 SATISFACTORY
A2 MIKE A2 UNSATISFACTORY
What i'm trying to achieve is that if there is a mix of SATISFACTORY & UNSATISFACTORY for 1 ID then the output only shows the UNSATISFACTORY. I've tried case statement, group by .... just having no luck
thanks once again.