Sql question ineed help! !

Question

1.00/5 (3 votes)

See more:

This is the exercise and i dont know how to solve it(is with mysql)

--Returns a list with all the employees along with the data of the
departments where they work. The list must include employees who do not have
no associated department and departments that do not have any employees
associated. Sort the listing alphabetically by department name

What I have tried:

 i tried this but it doest work 

SELECT employee.name, department.name
FROM employee and INNER JOIN department d
ON d.code=e.department_code
WHERE e.department_code AND d.code IS NULL
ORDER BY d.name DESC;

Posted 15-Mar-22 7:15am

Medina Rojas Luz María Concepción

Updated 17-Mar-22 4:56am

Add a Solution

Comments

CHill60 15-Mar-22 13:42pm

Instead of saying "it doesn't work" share the sample data, the results you would expect from that data and the actual results you got. We don't have access to your database!

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2022-03-15T07:45:00

Firstly your syntax is incorrect

SQL

FROM employee and INNER JOIN department d

should be

SQL

FROM employee LEFT JOIN department d -- typo corrected

Your next problem is that you have been told to include employees who have no associated department - which means you should not use an INNER join but use a LEFT OUTER JOIN instead. You will get a department.name of NULL where there is no associated department - deal with that in your display layer

Edit: In response to the comments I have returned to give a more complete (and corrected) answer.
Firstly - read up on SQL syntax. You cannot have

SQL

SELECT employee.name, department.name

if you having given your tables aliases - that d after department is the alias. You also cannot have

SQL

ON d.code=e.department_code

unless you also have the alias e - there is no alias after employee.

Next problem

SQL

WHERE e.department_code AND d.code IS NULL

e.department code is not a boolean so I guess you were trying to say

SQL

WHERE e.department_code IS NULL AND d.code IS NULL

Which it never could be because you did an inner join on those very columns. This article may help to explain why INNER join would never work in this case Visual Representation of SQL Joins[^]

However, my suggestion of using a LEFT OUTER join was erroneous - I should have suggested a FULL OUTER JOIN instead. Why? Because you want everything from both tables - something like this

SQL

SELECT e.name, d.name
FROM @employee  e
FULL OUTER JOIN @department d
ON d.code=e.department_code
ORDER BY d.name DESC;

Note there is no WHERE clause here - we want everything

M Imran Ansari · Answer 2 · 2022-03-17T04:56:00

Solution 2

In addiion with solution-1, as per question 'Sort the listing alphabetically by department name' should be ordered as ascending to sort alphabetically like.

SQL

ORDER BY d.name ASC;

Posted 17-Mar-22 4:56am

M Imran Ansari