Is this the right way to list data from table1 via a key in table 2? (SQL)

73 views Asked by At

I'm a complete beginner to databases and SQL and I've been smashing my head against the wall for a while with this question. I've tried looking for the answer before posting this but all I could find were more complex questions with answers that didn't really apply to me. I apologize if by any chance this was already answered and I missed it.

Task: list names and last-names of all the workers that work in marketing.

Table1(workers)                                Table2(departments)  

ID    last-name    name    department#         department#     department-name

1     ...          ...          2                   2            marketing
2     ...          ...          5                   5               ...
3     ...          ...          8                   8               ...
4     ...          ...          6                   6               ...

This is what I wrote:

SELECT name, last-name
FROM workers, departments
WHERE departments.department# = workers.department# AND departments.department-name = "marketing"

Is this the correct way to write my query in this instance or is "departments" in the second line of the query redundant since I'm trying to list only names and last-names, which are both in the "workers" table and not "departments".

If it is the right way, is there a better, cleaner way to achieve the same result?

Thank you for your help.

edit: I've done some more reading and I'm curious if this would work, too:

SELECT name, last-name
FROM workers
WHERE department# IN
    (SELECT department#
    FROM departments
WHERE department-name LIKE "Marketing%");

Appreciate your time.

1

There are 1 answers

0
mcr On BEST ANSWER

I would suggest :

 SELECT name, last-name
    FROM workers INNER JOIN departments
                 ON workers.department# = departments.department#
    WHERE department-name = 'marketing'