Oracle SQL - Joins with condition

Question

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

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) — mangusta
– mangusta, Commented Nov 6, 2020 at 5:24
How are these two tables related? Is their any branch indicator in staff table? — Parfait
– Parfait, Commented Nov 6, 2020 at 5:29

Popeye · Accepted Answer · 2020-11-06 07:50:57Z

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';

answered Nov 6, 2020 at 7:50

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL - Joins with condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related