CODEWITHSUNDEEP
mysqlsqldatabase
Hailwood
Hailwood

Reputation: 92651

Non implicit join, return when right join is empty

I have two tables:

contactGroups cg and contactGroupLink cgl.

CREATE TABLE `contactgroups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `description` text,
  `dateCreated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

CREATE TABLE `contactgrouplink` (
  `groupId` int(11) NOT NULL,
  `ContactId` int(11) NOT NULL,
  PRIMARY KEY (`groupId`,`ContactId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

If a contact is in a group then an entry gets put into the cgl table with the contactId and the groupId.

This all works fine.

I am wanting to do a query that gets all information for all groups from the cg table where account = 1.

As part of this query I also want to add an extra row called number which has the number if entries in the cgl table for that group.

I am using 

SELECT
  cg.id AS id,
  NAME,
  description,
  dateCreated,
  COUNT(*) AS number
FROM 
  contactgroups cg,
  contactgrouplink cgl
WHERE 
  cg.account = 1
AND 
  cg.id = cgl.groupId
GROUP BY
  cg.id

Which works perfect as long as at least one contact exists for a group. otherwise the group is not returned.

What must I do to this query to make it return all groups?

Upvotes: 1

Views: 156

Answers (2)

Mark Byers
Mark Byers

Reputation: 838806

Use a LEFT JOIN to return a row for every member of the left table, even if there is no corresponding row in the right table.

In other words, use this query:

SELECT
  cg.id AS id,
  NAME,
  description,
  dateCreated,
  COUNT(cgl.groupId) AS number
FROM contactgroups AS cg
LEFT JOIN contactgrouplink AS cgl
ON cg.id = cgl.groupId
WHERE cg.account = 1

Of course this will only return all groups where cg.account = 1, but I assume that is what you meant by "all groups". (If you really want all groups, then omit the WHERE clause).

Upvotes: 1

DRapp
DRapp

Reputation: 48169

Use a LEFT JOIN, and the IFNULL() will prevent null values on conditions no such matching records in cgl aliased table

SELECT   
      cg.id AS id,   
      NAME,   
      description,   
      dateCreated,   
      IFNULL( COUNT(*), 0 ) AS number 
   FROM    
      contactgroups cg
         LEFT JOIN contactgrouplink cgl 
            ON cg.id = cgl.groupID
   WHERE    
      cg.account = 1

Upvotes: 1

Related Questions