CODEWITHSUNDEEP
sqlmultiple-tables
user1375026
user1375026

Reputation: 413

SQL Plus Query - Multiple Table Query

This is causing me more trouble then it should.

I have the following sample tables:

____________________       ____________      
|   Name|   Number |       | Number   |
|_______|__________|       |__________|
| Alice |     1    |       |    1     |
| Bob   |     2    |       |    1     |
|_______|__________|       |__________|

I want my result to be:

_____________________________________
| Name   |   Number  | Count(Number) |
|________|___________|_______________|
| Alice  |     1     |      2        |
| Bob    |     2     |      0        |
|________|___________|_______________|

I'm going back and forward but I'm sure this shouldn't be so tricky. I assume I'm missing something.

I've modified Gordon's answer:

 select name, number count(t2.number)
 from table1 t1, 
 table2 t2 
 where t1.number = t2.number (+)
 group by t1.number;

Upvotes: 0

Views: 2139

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You need a join and aggregation. However, the join needs to be a left outer join to keep all the rows:

select name, number, count(t2.number)
from table1 t1 left outer join
     table2 t2
     on t1.number = t2.number
group by t1.name, t1.number;

And, the count() is counting the non-NULL values in the second table, so you can get 0 when there is no match.

Upvotes: 1

Related Questions