list records which do not meet a certain criteria in Oracle SQL

user_19792723 0
Viewed 36

I got 3 tables please see the below. Currently, a car has a unique tag assigned to them. I have employees with multiple roles. There are only 3 roles. 1 = Surveyor, 2 = Admin or 3 = Engineer.

A car can be assigned to more than one employee. One car can have a surveyor, admin and engineer using it. But I would like to have a list of all car tags that do not have a surveyor.

CarTags
tag
company_name
account_id
Employees
id
name
email
account_id
role_id
Roles
employee_id
role_name
role_id

For the column role_id in the Roles table, there can be 3 options. 1 = Surveyor, 2 = Admin or 3 = Engineer

The below Oracle SQL query returns me a list of all car tags, company names, employee id, employee names, employee emails and role id's but how can I get a list of all car tags which do not have a surveyor assigned to it?

SELECT
    cartags.tag                  AS "TAG Name",
    cartags.company_name         AS "Company Name",
    Employees.id                 AS "Employee ID",
    Employees.name               AS "Employee Name",
    Employees.email              AS "Email Address",
    Roles.id                     AS "Role ID"
FROM
    Employees INNER JOIN cartags ON cartags.account_id = Employees.acount_id
    INNER JOIN Roles ON roles.employee_id = Employees.id
oracle-sqldeveloper oracle sql database
1 Answers

I used CTE (common table expression) to get a list of account_id with a Surveyor in it. Then on your query, I will use that cte table to exclude accounts_ids in the where condition.

WITH Excluded as (
SELECT 
    Employees.account_id
FROM 
    Employees
    INNER JOIN Roles  ON Roles.employee_id = Employees.id
WHERE 
    Roles.role_id = 1
)
SELECT
    cartags.tag                  AS "TAG Name",
    cartags.company_name         AS "Company Name",
    Employees.id                 AS "Employee ID",
    Employees.name               AS "Employee Name",
    Employees.email              AS "Email Address" 
FROM
    Employees 
    INNER JOIN CarTags ON CarTags.account_id = Employees.account_id
    INNER JOIN Roles ON Roles.employee_id = Employees.id
    INNER JOIN Excluded ON Excluded.account_id <> Employees.account_id

sample output:  
AG Name Company Name    Employee ID Employee Name   Email Address
tag2    company2        4           Admin adam  Admina2dam@adm.com
tag2    company2        5           Engr adam   Engr2adam@adm.com
tag3    company3        6           Admin adam  Admin3adam@adm.com

See sample data here

user_73718930
Related