Help with query

Hello I need some help to make a query for these 2 tables:
TblEmployee
ID
EmployeeName
TblEmployeeChild
ID
SubjectID
Mark
I want to select Employeename, and count of tblEmployeeChild.ID where
subjectID > 5
But I also want to list all the employeeName and display count as 0.
Even though I use Left outer join, it only displays the EmployeeName for the
record for which the where condition is true.
How do I fix this ?
ThanksI'm just guessing because you've given us no information about primary
or foreign keys. Put the subjectid criteria after ON rather than WHERE:
SELECT E.id, E.employeename, COUNT(C.id)
FROM tblEmployee AS E
LEFT JOIN tblEmployeeChild AS C
ON E.id = C.id
AND C.subjectid > 5
GROUP BY E.id, E.employeename ;
David Portas
SQL Server MVP
--|||union your first query
to another query
which is not in your first query
then assigne the value to zero
--pseduocode--
first query
union
secondquery, where not in first query, count=0
--pseduocode--
"news.microsoft.com" wrote:

> Hello I need some help to make a query for these 2 tables:
> TblEmployee
> ID
> EmployeeName
>
> TblEmployeeChild
> ID
> SubjectID
> Mark
>
>
> I want to select Employeename, and count of tblEmployeeChild.ID where
> subjectID > 5
> But I also want to list all the employeeName and display count as 0.
> Even though I use Left outer join, it only displays the EmployeeName for t
he
> record for which the where condition is true.
> How do I fix this ?
> Thanks
>
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Plese stop using those silly :"tbl-" prefixes -- it violates ISO-11179
rules and there is only one data structure in SQL anyway. Do you really
have a single employee who is a piece of furniture? Why don't you know
that a row is not a record? Did you mean this:
CREATE TABLE Personnel
(emp_id INTEGER NOT NULL PRIMARY KEY,
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL,
etc.);
CREATE TABLE Dependents
(emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
dependent_nbr INTEGER NOT NULL
CHECK (dependent_nbr > 0),
PRIMARY KEY (emp_id, dependent_nbr),
subject_id INTEGER NOT NULL
CHECK (subject_id > 0),
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL,
etc.);
subjectID > 5 <<
SELECT P.last_name, P.first_name,
COUNT(dependent_nbr) AS dependent_tally
FROM Personnel AS P
LEFT OUTER JOIN
Dependents AS D
ON D.subject_id > 5
AND P.emp_id = D.emp_id
GROUP BY P.last_name, P.first_name;|||Perfect!
Wroked like a charm, learnt we can put condition on the join itself, thanks
:)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124106846.435010.111810@.g47g2000cwa.googlegroups.com...
> I'm just guessing because you've given us no information about primary
> or foreign keys. Put the subjectid criteria after ON rather than WHERE:
> SELECT E.id, E.employeename, COUNT(C.id)
> FROM tblEmployee AS E
> LEFT JOIN tblEmployeeChild AS C
> ON E.id = C.id
> AND C.subjectid > 5
> GROUP BY E.id, E.employeename ;
> --
> David Portas
> SQL Server MVP
> --
>