SQL Server - Is there a way to do a row by row comparison (with multiple constraints) to find what doesn't exist? -

is there way return if there isn't row person 1 of subjects if compiled (see below in table). theoretically, want result of code return

name      state subject doe, john tx    history 

should adding new column table 2 or table can cross walk me these results?

right have multiple tables made selecting subject = blah, hoping keep larger table because have 10 subjects go through.

tbl1

name    state   subject grade doe, john   tx  math    0.45 doe, john   tx  science 0.85 doe, jane   ms  math    0.45 doe, jane   ms  science 0.85 doe, jane   ms  history 0.75 

tbl2

doe, john   tx doe, jane   ms 

thanks

you can using cross join combinations , filtering out ones exist. instance:

select ns.*, s.* (select distinct name, state t) ns cross join      (select distinct subject t) s not exists (select 1                   t                   t.name = ns.name , t.state = ns.state , t.subject = s.subject                  );