CODEWITHSUNDEEP
mysqlsqlinner-join
Jhonny Jr.
Jhonny Jr.

Reputation: 306

MySQL Inner Join 2 Tables and Count Table 2 by ID from Table 1

I have two table, and want to join both of them. Then i want to get the count total result from Table B if one or more data from Table A is have the same ID on Table B. Here is the example.

enter image description here

enter image description here

enter image description here

I've already try like this, but it only give me two result, not like what i want:

SELECT 
table_a.*,
table_b.*,
count(*) as total
FROM 
    table_a,
    table_b
WHERE
    table_b.id_tblA = table_a.id_tblA
GROUP BY
    table_b.id_tblB

Upvotes: 0

Views: 897

Answers (2)

Judith Palacios
Judith Palacios

Reputation: 413

try this

SELECT
    table_a.id_tblA, table_a.tblA_name, 
    (SELECT count(*) FROM table_b WHERE table_b.id_tblA = table_a.id_tblA)
FROM
    table_a;

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

you should use a left join and case when 

  SELECT 
  table_a.id_tblA,
  table_a.Name,
  sum(case when table_b.id_tblB is null then 0 else 1 end) total
  FROM  table_a
  left  join table_b on table_b.id_tblA = table_a.id_tblA
  group by table_a.id_tblA, table_a.Name

Upvotes: 1

Related Questions