SQL Server get non-matching values from two tables -

here's scenario: in sql server 2008 r2 database, table has studyid, visitcode , visitsequencenumber. table b has studyid, subjectid , visitcode.

table has possible visitcodes given studyid (there on 200 studyids in table, each own set of visitcodes).

table b has subjectids given studyid, visitids each subject (there on 200 studyids in table, each own set of subjectids).

i need create list of subjectids studyid, aling visitids subjectid not have. can't make list of subjectids missing visits, need identify visits missing each subject.

so if studyid 'c1234' table has this:

 studyid | visitcode | visitsequencenumber c1234   | v100a     | 100 c1234   | v110a     | 110 c1234   | v120a     | 120 c1234   | uns       | 999 

and table b has:

 studyid |subjectid | visitcode c1234   | 01-001   | v100a c1234   | 01-001   | v120a c1234   | 01-001   | uns c1234   | 01-002   | v110a c1234   | 01-002   | uns 

i need return rows contain following:

 studyid | subjectid | visitcode c1234   | 01-001    | v110a c1234   | 01-002    | v100a c1234   | 01-002    | v120a 

for reason can't seem work 1 out - how match subjects missing visits when definition not in table b subjects? appreciated!

you have introduce kind of master list required subjectids , visitcodes.

edit

i possible values studyid tableb (here: limited 'c1234'), values subjectid tableb again , values visitcodes tablea. after run join between these possible values , check whether combination exists in tableb

select sti studyid, sid studyid, vc visitcode   ( select distinct studyid sti tableb ) stis  inner join   ( select distinct studyid ssi,subjectid sid tableb) s on ssi=sti  inner join   ( select distinct studyid vsi,visitcode vc tablea ) v on vsi=sti not exists (select 1 tableb         studyid=sti , subjectid=sid , visitcode=vc) , sti = 'c1234' -- limit example current study 

see here working demo (mysql): http://sqlfiddle.com/#!9/cc91b/11
or here (t-sql 2014): demo on data.stackexchange