Can this be done?

[sharpweb]

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.""

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;

[DonkeyTeeth]

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.""

[sharpweb]

Thanks! Worked Great!