CODEWITHSUNDEEP
sqljoinselectteradata
Andre G
Andre G

Reputation: 31

Select rows with "ONLY" a condition

I've just started learning SQL, and i'm using teradata trial database to practice ( using the db_pvfc9_std database after you click "Execute Trial" ). Here is a link to a document with the database schema.

I have a couple of queries with a only clause, e.g:

QUERY: What are the names of employees who can only use 12in BandSaw?

so, this is what i have so far: 

SELECT Employee_Name
FROM   EMPLOYEE_T as ETT, EMPLOYEE_SKILLS_T as EST, SKILL_T as ST
WHERE  ETT.Employee_ID = EST.Employee_ID and EST.Skill_ID = ST.Skill_ID and ST.Skill_Description = 
'12in Band Saw'

i think i'm getting employees who can use 12in band saw, but i don't know how to implement the 'only' part, where i wont get those who can also use other kinds.. can someone explain?

thanks!

Upvotes: 1

Views: 60

Answers (2)

GMB
GMB

Reputation: 222682

You could use aggregation:

select ett.employee_name
from employee_t as ett
inner join employee_skills_t as est on ett.employee_id = est.employee_id
inner join skill_t as st on est.skill_id = st.skill_id
group by ett.employee_name
having 
    min(st.skill_description) = max(st.skill_description)
    and min(st.skill_description) = '12in Band Saw'

The query joins the table, then groups by employee; the, the having clause ensures that there is only one distinct skill per group, and that it corresponds to the sought value.

Note that this query uses standard joins (with the on keywords) rather than implicit join (with commas in the from clause). As you are just starting to learn SQL, this is a habbit to permanently embrace.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.

Simple do not learn commas. Period.

What you want is aggregation. I think:

SELECT Employee_Name
FROM EMPLOYEE_T ETT JOIN
     EMPLOYEE_SKILLS_T EST
     ON ETT.Employee_ID = EST.Employee_ID JOIN
     SKILL_T ST
     ON EST.Skill_ID = ST.Skill_ID 
GROUP BY Employee_Name
HAVING MIN(ST.Skill_Description) = MAX(ST.Skill_Description) AND
       MIN(ST.Skill_Description) = '12in Band Saw';

Upvotes: 2

Related Questions