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

Question

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.

Pரதீப் · Accepted Answer · 2015-02-02 12:30:33Z

1

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

answered Feb 2, 2015 at 12:30

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Schmelter · Accepted Answer · 2015-02-02 12:31:01Z

1

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

answered Feb 2, 2015 at 12:31

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

3 Comments

Vishal Zanzrukia Over a year ago

if we have 10K records, then above sub query will execute for 10K times..!

Tim Schmelter Over a year ago

@VishalZanzrukia: currently the table contains two records ;) I'm more familiar with SQL-Server where this is not an issue.

Vishal Zanzrukia Over a year ago

but I think it is just for question understanding purpose.

Vishal Zanzrukia · Accepted Answer · 2015-02-02 12:33:21Z

0

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

answered Feb 2, 2015 at 12:33

Vishal Zanzrukia

4,9835 gold badges41 silver badges83 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related