Not Exist SQL?

[jsdba]

See attached picture:

I need the SQL statement to select JobNo and JobCategoryID that does not have EmpRoleID 1. The attached picture displays the table with highlighted record i would need to select. I only want the JobNo and JobCategoryID, i dont want EmpRoleID in my results

I think i can do SELECT DISTINCT WHERE EmpRoleID <> 1 and omit the EmpRoleID from results but i wondering if there is a better way of writing this query without using DISTINCT.

 

[Minty]

The query you are suggesting wouldn't only bring in that one record. Your criteria would bring in any records with an EmpRoleID that was 2 or 3 or anything else other than 1 ?

 

[jsdba]

The query you are suggesting wouldn't only bring in that one record. Your criteria would bring in any records with an EmpRoleID that was 2 or 3 or anything else other than 1 ?

Let me try again. I need JobNo and JobCategory where that grouping ONLY has EmpRoleID = 3.

The highlighted record would be an example of that

 

[Minty]

Ah - that makes more sense. You'll need a select sub query to pick any values <> 3, then use that as a Not In() - Untested but something like

SELECT JobNo ,JobCategoryID from YourTable WHERE JobNo Not In(SELECT Distinct t.JobNo From YourTable as t Where t.EmpRolID <>3)

This would't work if you have nulls in your data.

 

[arnelgp]

SELECT JobNo ,JobCategoryID from YourTable WHERE EmpRoleID = 3 And JobCategoryID = EmpRoleID Group by JobNo ,JobCategoryID: