How do i join two tables in such a way that all the rows are present in the output

0
votes

I have two table say TableA and TableB with below structure

TableA

cust_name
cust_id
cust_age

TableB

id
cust_id
balance

I need to join these two tables all retrieve the below columns

tableA.cust_name,tableA.cust_age,tableB.balance

but if I use the below query

select a.cust_name,a.cust_age,sum(b.balance) from tableA a,tableB b where a.cust_id=b.cust_id and b.id = (select max(id) from tableB where cust_id=b.cust_id)

I only get those rows which are present in both the tables, but i need all the rows where there is a customer in tableA and not in tableB the b.balance should come either null or 0.

1
sqloracle
I don't understand your edit. If you restrict b.id = (select max(id)...) then you will match a single row on b, so you're summing one value, which is pointless. Or you won't match any rows. What are you really trying to do? Just get the most recent balance for each customer? If so that's a pretty substantial change from what you originally asked. - Alex Poole

1 Answers

2
votes

I would say Left join

SELECT a.cust_name, a.cust_age, sum(nvl(b.balance,0)) 
FROM tableA a
LEFT JOIN tableB b 
  ON a.cust_id=b.cust_id
GROUP BY a.cust_name, a.cust_age

Also use NVL as you may get some nulls in b.balance.

Alternatively if you are on an old Oracle version you have to use a different join:

SELECT a.cust_name, a.cust_age, sum(nvl(b.balance,0)) 
FROM tableA a, tableB b 
where a.cust_id=b.cust_id (+)
GROUP BY a.cust_name, a.cust_age