Can this be done?

I've written myself an SQL that works perfectly, unless there are no entries in the child table. There is always a client for each job. There isn't always a child for each client. For each job I want to return the number of childs for the client, or 0 (zero) if there are none. This sql statement does everything but return 0 for those without any children. I'm trying to do this with the "(k.Client_Id = j.Client_Id OR NULL)" part, but its late and I'm tired. A little help would be great!!!

I can easily do this with two sql statements, but I need to be able to order by each column thru the php variable $ob.

Here is my sql statement so far:

"SELECT c.Client_Id, j.id, c.fname, c.lname, j.startdate, j.starttime, 
j.created, COUNT(*) as shifts,  COUNT(DISTINCT j.startdate) AS days, 
 COUNT(DISTINCT k.id) AS children 
FROM nn_job j, nn_client c, nn_child k 
WHERE c.Client_Id = j.Client_Id AND  (k.Client_Id = j.Client_Id OR NULL) 
GROUP BY j.Client_Id ORDER BY ".$ob.""

Code (markup):

And here are the table definitions:

-- 
-- Table structure for table 'nn_child'
-- 

CREATE TABLE nn_child (
  id int(11) NOT NULL auto_increment,
  Client_Id varchar(50) NOT NULL default '0',
  fname varchar(100) NOT NULL default '',
  birthdate date NOT NULL default '0000-00-00',
  age varchar(100) NOT NULL default '',
  PRIMARY KEY  (id)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table 'nn_client'
-- 

CREATE TABLE nn_client (
  id int(11) NOT NULL auto_increment,
  Client_Id varchar(50) NOT NULL default '',
  fname varchar(100) NOT NULL default '',
  lname varchar(100) NOT NULL default '',
  PRIMARY KEY  (id)
) TYPE=MyISAM;

-- --------------------------------------------------------

-- 
-- Table structure for table 'nn_job'
-- 

CREATE TABLE nn_job (
  id int(11) NOT NULL auto_increment,
  Client_Id varchar(50) NOT NULL default '0',
  startdate date NOT NULL default '0000-00-00',
  starttime int(11) NOT NULL default '-1',
  location varchar(255) NOT NULL default '',
  roomnumber varchar(50) NOT NULL default '',
  blurb text NOT NULL,
  filled tinyint(4) NOT NULL default '0',
  created date NOT NULL default '0000-00-00',
  PRIMARY KEY  (id)
) TYPE=MyISAM;

Code (markup):

 

Try JOINs instead of a simple =:

SELECT c.Client_Id, j.id, c.fname, c.lname, j.startdate, j.starttime,
j.created, COUNT(*) as shifts, COUNT(DISTINCT j.startdate) AS days,
COUNT(DISTINCT k.id) AS children
FROM nn_job j
LEFT JOIN nn_client c ON j.Client_Id = c.Client_Id
LEFT JOIN nn_child k ON j.Client_Id = k.Client_Id
GROUP BY j.Client_Id ORDER BY ".$ob.""

 

Thanks! Worked Great!