SQL join count field

MS SQL 2000, VFP 8
2 tables
1.parent:
employee_id
first_name
last_name
etc.
2.child:
child_id
employee_id
checked (L)
etc.

I'd like to get remote view
all records from parent table (left outer join)
columns:
first_name,last_name,(count total related records in child),(count related
records in child where not checked)

How to do this?

Christian
Thu Jan 29 04:26:19 CST 2004

Hello,

this should work ..

SELECT em.employee_id, em.first_name, em.last_name, COUNT(ch.employee_id) AS
nRecCount, SUM(CASE ch.Checked WHEN 0 THEN 0 ELSE 1) AS nCheckCount FROM
employee em LEFT OUTER JOIN child ch ON em.employee_id = ch.employee_id
GROUP BY em.employee_id, em.first_name, em.last_name

there one thing to mention ..
if the number of records in the child table is 0 the checked count will not
be 0 but .NULL. since no sum can be build

Regards

Christian


"Erlandas" <erlastoun@hotmail.com> schrieb im Newsbeitrag
news:#QQ07tk5DHA.2412@TK2MSFTNGP11.phx.gbl...
> MS SQL 2000, VFP 8
> 2 tables
> 1.parent:
> employee_id
> first_name
> last_name
> etc.
> 2.child:
> child_id
> employee_id
> checked (L)
> etc.
>
> I'd like to get remote view
> all records from parent table (left outer join)
> columns:
> first_name,last_name,(count total related records in child),(count related
> records in child where not checked)
>
> How to do this?
>
>

Cindy
Thu Jan 29 10:12:47 CST 2004

In news: bvan4j$17j$07$1@news.t-online.com,
Christian Ehlscheid <ehlscheid-no-spam@edv-ermtraud.de> wrote:
> SELECT .....SUM(CASE ch.Checked WHEN 0 THEN 0
> ELSE 1) AS nCheckCount ....
>
> there one thing to mention ..
> if the number of records in the child table is 0 the checked count
> will not be 0 but .NULL. since no sum can be build


USE the FoxPro NVL() or the SQL Server ISNULL(). This assumes that when a
child record is not present then you don't count the missing record as .F.:

CREATE CURSOR ParentTbl(Pk I)
INSERT INTO ParentTbl VALUES (1)
INSERT INTO ParentTbl VALUES (2)
CREATE CURSOR ChildTbl(Fk I, Checked L)
INSERT INTO ChildTbl VALUES(1, .F.)
INSERT INTO ChildTbl VALUES(1, .T.)

SELECT ;
ParentTbl.Pk, ;
SUM(IIF(NVL(ChildTbl.Checked, .T.), 0, 1)) AS nCheckCount ;
FROM ParentTbl ;
LEFT JOIN ChildTbl ;
ON ParentTbl.Pk = ChildTbl.Fk ;
GROUP BY ParentTbl.Pk

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy.winegarden@mvps.org www.cindywinegarden.com

Erlandas
Fri Jan 30 06:47:53 CST 2004

MS SQL 2000, VFP 8
This example working within VFP, but incorrect syntax with MSSQL remote view
SUM(IIF(NVL(ChildTbl.Checked, .T.), 0, 1)) AS nCheckCount.
Same error with SUM(CASE ch.Checked WHEN 0 THEN 0 ELSE 1) AS nCheckCount.

something wrong here.
do you have another suggestion?

"Cindy Winegarden" <cindy.winegarden@mvps.org> wrote in message
news:%23dqeVQo5DHA.2696@TK2MSFTNGP09.phx.gbl...
> In news: bvan4j$17j$07$1@news.t-online.com,
> Christian Ehlscheid <ehlscheid-no-spam@edv-ermtraud.de> wrote:
> > SELECT .....SUM(CASE ch.Checked WHEN 0 THEN 0
> > ELSE 1) AS nCheckCount ....
> >
> > there one thing to mention ..
> > if the number of records in the child table is 0 the checked count
> > will not be 0 but .NULL. since no sum can be build
>
>
> USE the FoxPro NVL() or the SQL Server ISNULL(). This assumes that when a
> child record is not present then you don't count the missing record as
.F.:
>
> CREATE CURSOR ParentTbl(Pk I)
> INSERT INTO ParentTbl VALUES (1)
> INSERT INTO ParentTbl VALUES (2)
> CREATE CURSOR ChildTbl(Fk I, Checked L)
> INSERT INTO ChildTbl VALUES(1, .F.)
> INSERT INTO ChildTbl VALUES(1, .T.)
>
> SELECT ;
> ParentTbl.Pk, ;
> SUM(IIF(NVL(ChildTbl.Checked, .T.), 0, 1)) AS nCheckCount ;
> FROM ParentTbl ;
> LEFT JOIN ChildTbl ;
> ON ParentTbl.Pk = ChildTbl.Fk ;
> GROUP BY ParentTbl.Pk
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy.winegarden@mvps.org www.cindywinegarden.com
>
>
>