如何根据子查询中的特定值过滤记录?

Scenario: I have a table of jobs which I want to fetch. Each job has one or more items associated with it which are stored in jobItems table. item's code and details are saved in items table. There is one-to-many relationship between jobs and jobItems tables. Also keep in mind, it has large dataset. I have a table of jobs which I want t