Filtering out records in first table that join to multiple records in second table

1

Hopefully asking this properly...

Essentially, I have two joined tables (Jobs and Materials)
All jobs use several materials, but only need to filter by jobs joined with a specific group of materials.

Some Jobs (records) use MaterialA while some jobs use MaterialA and MaterialB, or MaterialA and MaterialC, etc.

MaterialA is the natural resin, MaterialB, MaterialC, etc are the color additives, and I am trying to list jobs that have the color and ignore the natural only jobs.
Then I can see pounds of natural resin used that also has a color mixed in

Question:
Can I filter so that I can find/list those jobs that use MaterialA and MaterialB (and/or MaterialA and MaterialC, etc.) , ignoring the jobs that only use MaterialA

2

Sure you can. But you didn't specify how the tables are related, need to know that to write the code.

3

Ah! Thank you!!
Amongst other columns...

Table 1 "Job"
Job.Job

Table 2 "Material"
Material.Material
Material.Job
Material.Weight

Joining on:
Job.Job = Material.Job

4 

SELECT * /*m., j., ...*/
FROM Material m
INNER JOIN Job j ON J.job = m.job
WHERE m.Material IN ('A', 'B', 'C') AND
    EXISTS(SELECT * FROM Material m2 WHERE m2.job = m.job AND 
        ((m.material = 'A' AND m2.material IN ('B', 'C')) OR 
         (m.material IN ('B', 'C') AND m2.material = 'A')))
1 Like
5

Thank you again! I see WHERE EXISTS is the key coding where the required filtering is happening.

I am going to experiment with different material scenarios (multiple materials (Like %), material exclusions (not like %), etc.) but I may reach out again if I get stuck.

6

Using the suggestions above I was able to work out an option to adjust the code to include some extra filtering. Thought I would add it here for future reference.
Essentially was looking for a means to produce a list of current jobs and weights that use our natural resin and some form of colorant (via a "Class" field) while ignoring the jobs that use just the natural resin and no color.
Thanks again, Scott, for setting me on the right track. "EXIST" was new to me!

Also, it is probably a bit "wordy" (along with leaving out the aliases), to help me follow the logic a bit better, so if there are any improvements that can be made to the general code, please let me know.

SELECT        dbo.Job.Job, dbo.Material_Req.Material, dbo.Material_Req.Est_Qty
FROM            dbo.Material_Req INNER JOIN
                         dbo.Job ON dbo.Material_Req.Job = dbo.Job.Job INNER JOIN
                         dbo.Material ON dbo.Material_Req.Material = dbo.Material.Material
WHERE        (dbo.Job.Order_Date >= CONVERT(DATETIME, '2021-10-01 00:00:00', 102)) AND (dbo.Material_Req.Material LIKE 'Natural') AND EXISTS
                             (SELECT        Job_1.Job
                               FROM            dbo.Material_Req AS Material_Req_1 INNER JOIN
                                                         dbo.Job AS Job_1 ON Material_Req_1.Job = Job_1.Job INNER JOIN
                                                         dbo.Material AS Material_1 ON Material_Req_1.Material = Material_1.Material
                               WHERE        (job.job = Job_1.Job) AND (Job_1.Order_Date >= CONVERT(DATETIME, '2021-10-01 00:00:00', 102)) AND (Material_1.Class LIKE 'COLOR'))
ORDER BY dbo.Job.Job DESC