CODEWITHSUNDEEP
sqlpostgresql
amir hodaee
amir hodaee

Reputation: 47

join two sql table as column

i have two tables as below:

firsttable

id cat_id name
1 2 name_01
2 2 name_02
3 1 name_03
4 3 name_04
5 3 name_04

secondtable

id name
1 cat_01
2 cat_02
3 cat_03

my question is how can i create below table result?

id(secondtable) name(secondtable) count(firsttable)
1 cat_01 1
2 cat_02 2
3 cat_03 2

Upvotes: 0

Views: 79

Answers (3)

James
James

Reputation: 3015

Just using standard aggregation

select
  s.id,
  s.name,
  count(*)
from firsttable f
  join secondtable s
    on f.cat_id = s.id
group by s.id, s.name order by s.id

Upvotes: 1

Rajeev Singh
Rajeev Singh

Reputation: 1809

It's very Simple like that

SELECT s.id,s.name, (SELECT count(*) 
FROM `firsttable` AS f 
WHERE f.cat_id = s.id ) as count 
FROM `secondtable` AS s

Upvotes: 0

Anis Belkebir
Anis Belkebir

Reputation: 36

select t2.id,t2.name,
(select count(*) from firsttable t1 where t1.cat_id=t2.id )as count
from secendtable t2;

Upvotes: 1

Related Questions