JCS JCS - 7 months ago 8
SQL Question

SQL How can i exclude an entire group of data when only one member of the group meets criteria

This is my first time asking a question on this site, so help me out if I'm missing information!

here is a simplified version of my current data set.

MajorCode Batch

1. 1500 360715
2. 6700 360715
3. 6800 360715
4. 1000 361098
5. 1000 361098
6. 1000 361098
7. 1000 361211
8. 1300 361211
9. 1300 361211


I want to limit my results so that they only show when at least one Major Code per Batch is >= 4000.

So, for the example above I would only expect to have only the 3 records for
Batch #360715
be returned.

If this is possible, please let me know!

Answer

In SQL SERVER you can do this with a Sub Query like so:

SELECT * 
FROM #YourTable
WHERE Batch IN (SELECT DISTINCT BATCH 
                FROM #YourTable 
                WHERE MajorCode >= 4000)
Comments