0

I'm sorry for asking these questions but I am really new and studying Oracle SQL for my university and I am having a hard time with a few things. Anyways, for this one, I am supposed to retrieve data from 2 different tables. One is from 'STAFF' and one is from 'BRANCH' I want the output to display the staff name (SNAME), the start date (STARTDATE) and the area (AREA). SNAME and STARTDATE are from the table 'STAFF', and the AREA is from the table 'Branch', how do I access that? Also, I ONLY want to display the names and start dates of those who are in the STOKE areea.

This is my code

SELECT SNAME, STARTDATE, BRANCH.AREA FROM STAFF CROSS JOIN BRANCH WHERE STAFF.BRANCHID = 20;

Note: The STAFF.BRANCHID = 20 is because the area 'STOKE' has a BRANCHID of 20.

This is what I get:

SNAME STARTDATE AREA ---------- --------- ------------- SMITH 15-NOV-00 ECCLESHALL JONES 02-MAR-01 ECCLESHALL SONG 03-JAN-02 ECCLESHALL SMITH 15-NOV-00 STOKE SONG 03-JAN-02 STOKE SMITH 15-NOV-00 STAFFORD

As you can see, the WHERE clause is not working because it outputs every area instead of stoke only.

I know I am supposed to use the JOIN function but I don't get which one and why, any useful links would be appreciated :)

Thank you

Improve this question
3
  • 1
    didn't get, why you use WHERE STAFF.BRANCHID = 20; instead of WHERE BRANCH.AREA = 'STOKE';. Also you need to specify a join condition which is ON STAFF.BRANCHID = BRANCH.ID I guess (you didn't specify the table schemas so I'm just guessing that there is ID column in table BRANCH) Commented Nov 6, 2020 at 5:24
  • Ahhh that's why it was doing that. Thank you! Commented Nov 6, 2020 at 5:28
  • How are these two tables related? Is their any branch indicator in staff table? Commented Nov 6, 2020 at 5:29

1 Answer 1

Reset to default
1

You need to use the INNER JOIN and proper join condition as follows:

SELECT S.SNAME, S.STARTDATE, B.AREA FROM STAFF S INNER JOIN BRANCH B ON S.BRANCHID = B.BRANCHID WHERE S.AREA = 'STOKE';
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.