SQL Query to get the compliance status

I’m trying to get the following query constructed

I got a table called client_vendor as follows. This stores the details of answers provided by client against a vendor for 4 questions.

client_vendor

create table client_vendor(clientid int, vendorid int, q1 varchar(1), q2 varchar(1), q2 varchar(1), q4  varchar(1));


insert into client_vendor values(100,1,"Y","N","N","");
insert into client_vendor values(101,1,"Y","Y","N","Y");
insert into client_vendor values(102,1,"Y","Y","","");

The client can answer either Y or N or not give a response to the questions asked. Here clientid=100 has answered as follows q1=Y,q2=N,q3=N,q4="" against vendorid=1.

We have another table called vendor_compliance

vendor_compliance

create table vendor_compliance(vendorid, q1 varchar(1), q2 varchar(1), q2 varchar(1), q4  varchar(1));

insert into vendor_question values(1,"Y","","N","");

This table indicates compliance,if the client answers for the questions the expected values.

Here if the client answers to q1=Y and q3=N then he is compliant. The answers to q2 and q3 are irrelevant for vendorid=1 to indicate compliance.

I wish to create a query which will show up the clientid, vendorid and the compliance status as follows

clientid,vendorid,compliance_status
100    ,1       ,compliant
101    ,1       ,compliant
102    ,1       ,non-compliant

Answer

You can use join with some case logic:

select cv.*,
       (case when vc.q1 <> cv.q1 and vc.q1 <> '' then 'non-compliant'
             when vc.q2 <> cv.q2 and vc.q2 <> '' then 'non-compliant'
             when vc.q3 <> cv.q3 and vc.q3 <> '' then 'non-compliant'
             when vc.q4 <> cv.q4 and vc.q4 <> '' then 'non-compliant'
             else 'compliant'
        end) as compliance
from client_vendor cv join
     vendor_compliance vc
     on cv.vendorid = vc.vendorid