CODEWITHSUNDEEP
mysqlsql
Niraj Thakar
Niraj Thakar

Reputation: 168

How to find non-existing data from another Table in mysql

I have 2 tables groups and contact and i fetch all groups with their total number of contact in contact table. Like in groups table i have values

  group1
  group2

in contact table i have

  myname group1
  myname1 group1
  myname2 group1

now i want all groupname with their contact count like

  group1 3
  group2 0

i used :

SELECT g.gid,g.groupname,g.TYPE,g.DATE,COUNT(*)AS cnt,1 
FROM groupname g,contacts c 
WHERE g.gid=c.gid AND uid=1 GROUP BY groupname

But i got those group which have value.

Upvotes: 0

Views: 45

Answers (3)

Vishal Zanzrukia
Vishal Zanzrukia

Reputation: 4973

SELECT g.gid,g.groupname,g.TYPE,g.DATE,COUNT(*)AS cnt,1 
FROM groupname g 
LEFT JOIN contacts c on c.group_name = g.group_name 
WHERE g.gid=c.gid AND uid=1 GROUP BY groupname

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460340

You can use a correlated sub-query:

SELECT g.gid, g.groupname, g.TYPE, 
       (SELECT COUNT(*) 
        FROM contacts c
        WHERE c.gid = g.gid) AS cnt
FROM groupname g
WHERE  uid=1

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

use Left Outer Join instead of old style of Inner Join

SELECT g.groupname,
       Count(c.gid) as Cnt
FROM   groups g
       LEFT OUTER JOIN contact c
                    ON g.gid = c.gid
WHERE  uid = 1
GROUP  BY g.groupname

Upvotes: 1

Related Questions