The 365 Data Science team is proud to invite you to our own community forum. A very well built system to support your queries, questions and give the chance to show your knowledge and help others in their path of becoming Data Science specialists.
Ask
Anybody can ask a question
Answer
Anybody can answer
Vote
The best answers are voted up and moderated by our team

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

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

Super Learner
0
Votes
2
Answer

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
 

2 Answers

365 Team
0
Votes

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

Thank you. Martin. The code runs, which is great! I have to review it to make sure I understand it since I have moved ahead in the lessons… But when I do run it, the results also return records containing solely “Engineer” as well as “Senior Engineer.” This is in fact, what I wanted to validate that I could not see using just the first and last names. Any ideas why the other values are being returned in the query as well?

2 weeks

365 Team

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

Thank you! It does… Now I will try to figure out why. 😉

2 weeks

Great! Thank you for letting us know! Good luck and please feel free to post another question should you encounter other difficulties. Thank you.
Best,
Martin

2 weeks
×
Learn Data Science
this Summer!
Get 50% OFF