Resolved: Sometimes I would like to validate my results. How can I do this in this example (Exists-Not Exists)

Question

I can often get my exercise query to work but am not always certain whether the result set is accurate base solely on the number of records returned. In the case of this exercise, I wanted to add the employee title to the result as a check. I could not figure out how to integrate this code into the query. Can this be done efficiently? This is my attempt. SELECT
e.first_name, e.last_name, t.title
FROM
employees e
INNER JOIN
titles t ON e.emp_no = t.emp_no and t.title = 'Assistant Engineer'
WHERE
EXISTS( SELECT
t.title
FROM
titles
WHERE
t.emp_no = e.emp_no
AND t.title = 'Assistant Engineer'); Workbench times out after 30 sec., so I am guessing this is not the most efficient code. Thank you

Answer 1

Hi Mike! Thanks for reaching out. Please accept my apologies for the delayed response. We can't add a condition when designating the columns we are joining our tables on. Moreover, we have already designated the title of interest in the subquery. That said, I guess that the following query will solve the task you are referring to.

SELECT 
e.first_name, e.last_name, t.title
FROM
employees e
JOIN
titles t ON e.emp_no = t.emp_no
WHERE
EXISTS( SELECT 
*
FROM
titles t
WHERE
t.emp_no = e.emp_no
AND title = 'Assistant Engineer');

Hope this helps.
Best,
Martin

Answer 2

Hi Mike! Thanks for your reply. Do you think that the following query delivers the output you are aiming for? Thank you.

SELECT 
e.first_name, e.last_name, t.title
FROM
employees e
JOIN
titles t ON t.emp_no = e.emp_no
WHERE
title = 'Assistant Engineer';

Looking forward to your answer.
Best,
Martin

Resolved: Sometimes I would like to validate my results. How can I do this in this example (Exists-Not Exists)

Submit an answer