CODEWITHSUNDEEP
sqldatabaseoracle-database
gravity00
gravity00

Reputation: 47

How would I select from two different tables and get the following in Oracle

I need to do the following:

From each branch, find the manager names and the #customers they are managing and the total deposit balance of the customers they manage

My database looks like this

drop table branch;
CREATE TABLE branch (
BNO NUMBER(1,0),
MANAGER_NAME VARCHAR(6),
Salary NUMBER(6,0),
MGRSTARTDATE TIMESTAMP (2)
);


INSERT INTO branch VALUES
(1,'BOB',100000,'19-JUN-2001');
INSERT INTO branch VALUES
(2,'CHRIS',150000,'01-Jan-2005');
INSERT INTO branch VALUES
(3,'ANGELA',90000,'22-May-1998');
INSERT INTO branch VALUES
(4,'KIM',90000,'29-May-1996');

drop table account;
CREATE TABLE account (
ACC NUMBER(3,0),
CNAME VARCHAR(4),
BNO NUMBER(1,0),
BALANCE NUMBER(4,0)
);

INSERT INTO account VALUES
(101,'LISA',1,100);
INSERT INTO account VALUES
(102,'LISA',2,500);
INSERT INTO account VALUES
(103,'TOM',1,400);
INSERT INTO account VALUES
(104,'JOHN',3,1200);
INSERT INTO account VALUES
(105,'TOM',3,900);

All I have so far and don't know what to do next is 

SELECT MANAGER_NAME
FROM branch;

I think I need to do some type of join but don't know how.

Upvotes: 1

Views: 38

Answers (2)

hoangnh
hoangnh

Reputation: 249

Try this

select br.manager_name, acc.cname from account acc 
left join branch br 
ON acc.bno = br.bno

And the below will sum balance that manager manage

select br.manager_name, acc.cname, sum(acc.balance) from account acc 
left join branch br 
ON acc.bno = br.bno
group by br.bno, br.manager_name

Upvotes: 2

epan9
epan9

Reputation: 1

You'll want to use a OUTER JOIN (this is the same as a LEFT/RIGHT join). An outer join takes all values from the one table and adds data from the second table matching on a key. The key you'd use to match is the "BNO" value -- hoangnh's example covers this

For reference, the other type of join is an INNER JOIN which wouldn't work since that would only return values with key matches in both tables (in your example, doing an inner join would exclude BNO=4 from the final result. Doing an outer join would have BNO=4 included with nulls for the customer values)

Upvotes: 0

Related Questions