sql server - How to filter records when foreign table has combined primary key in SQL -

i have 2 tables interlinked:

table #1: checklist

id_checklist(pk) |  name_checklist |  version(pk) 1                   xyz               1.0.0 1                   xyz               1.1.0 1                   xyz               1.2.0 2                   pqr               1.0.0 3                   abc               1.1.0 

table #2: machine_checklist

id_machine |  id_checklist(foreign key) |  version(foreign) 1              1                           1.2.0 1              3                           1.1.0 2              1                           1.1.0 

now want of remaining checklist not included in id_machine = 1

so query this:

select      id_checklist, name_checklist, version     checklist      (id_checklist not in (select mc.id_checklist                            machine_checklist mc                            mc.id_machine = '1'))     or (version not in (select mc.version                          machine_checklist mc                           mc.id_machine = '1')) 

this query not return proper results.

i pass id_machine dynamically. cannot pass version , that's problem.

what doing wrong in query?

you'd want left join tables on composite key, , null value on right table in join. tell missing data.

select      checklist.id_checklist, checklist.name_checklist, checklist.version     machine_checklist  left join checklist on machine_checklist.id_checklist = checklist.id_checklist      , machine_checklist.version = checklist.version     id_machine = 1 , checklist.id_checklist null