Returning customized results with SQL Server -

this little complicated i'm going break down. i'm trying results couldn't figure out query gonna like. premise this, users has purchased specific set of items, gear. when go site, see kits or setups users have submitted. want show them setups have gear they've purchased. don't need see setups gear not have. hope makes sense. here's tables like:

[gear]

• gearid unique key
• has list of gear (mics, heads, effects) unique id's each

[kits]

• kitid unique key
• has list of user submitted kits

[kitgearlink]

this table connects [kits] , [gear] each other. table lists out gear user submitted kit has.

[users]

• userid unique key
• list of users

[usergear]

links [users] , [gear] table together. stores user's personal gear consists of.

so how pull records each user show them kits work gear have. if kit has user doesn't own, won't show them. ideas?

thanks guys!

perhaps this:

select * kit k k.kitid not in (     select distinct kg.kitid     kitgearlink kg     left join (        select ug0.gearid        usergear ug0        ug0.userid = @userparam     ) ug on kg.gearid = ug.gearid     ug.gearid = null ) 

for given user id sub query return kits fail gear id join betwen user , kit (kits bit of gear user doesn't have). used filter list of kits in system.

edit: introduced second sub-query filter user gear user id parameter before left join occurs, see comments.