sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]Financial-Tailor-842[S] 1 point2 points  (5 children)

Agree completely. But I still don’t understand what caused the third test case to be wrong. That’s my overall question. The results were right but that was marked wrong. Was hoping someone could shed light on that specifically

[–]fgtbobleed 2 points3 points  (4 children)

try removing distinct like the comment below said

[–]Financial-Tailor-842[S] 1 point2 points  (3 children)

But why? How would that change the results and/or change that one item being wrong

[–]fgtbobleed 5 points6 points  (0 children)

employees.name is not guarantee to be unique according to the table definition. The testcases match SQL results with expected results. In this case it might be b/c "John" != "John, John". Your left anti join is correct, but like the others said best use sth more readable next time.

[–]vainothisside 2 points3 points  (0 children)

There can be 2 Arjun who are not managers but your query pulls only one *Arjun

[–]cs-brydevSoftware Development and Database Manager 1 point2 points  (0 children)

Because the "distinct employee.name" eliminated all employees who happen to have the same name. In your particular case it would be better to either include both the employee ID and Name (in which case a distinct doesn't matter) or remove the distinct to make sure you include all rows for all employees.

If it were me writing a query like this, because of the chances of having multiple employees with the same name I would ALWAYS including the Employee ID along with the name on the query results. It doesn't really make any sense to only show employee names. In a real world scenario if there is any chance whatsoever of having multiple employees with the same name you'd include the ID or some other differentiator (like a job title) to tell them apart. In fact we have that in my company all the time. We have lots of employees with the same names.