CODEWITHSUNDEEP
mysql
R9102
R9102

Reputation: 727

select from one table, count from another where id is not linked

Table 1

enter image description here

Table 2

enter image description here

Expected Output

Open | 2

Pending | 0

Closed | 0

so on....

I tried using this below query

SELECT d.status , COUNT(*) num,e.name  FROM table1 d  cross join table 2 e group by name;

Which resulted in

enter image description here

Can any one help me on this.

Upvotes: 1

Views: 2218

Answers (1)

lc.
lc.

Reputation: 116458

You need a left join. This type of join shows all rows from the left table, even when no rows from the right table exist.

select t2.name, count(t1.id)
from table2 as t2
left join table1 as t1 on t2.name = t1.status
group by t2.name

Note you need to aggregate on a column from table1 to produce the 0 desired, hence the count(t1.id). count(*) will produce 1 even if there is no row from table1.

A cross join as you have in your query simply creates a Cartesian product of the two tables involved, resulting in every row from the left table joined with every row in the right table once.

Upvotes: 5

Related Questions