COUNT 0 !!!! 1

[pondi]

This is my query:
___________________________________________________________
SELECT elt1.name, COUNT(elt2.name)
FROM (elementlinks AS el_link INNER JOIN elements AS elt1 ON el_link.n1=elt1.id)
INNER JOIN elements AS elt2 ON el_link.n2=elt2.id

WHERE elt1.typeid=2 AND elt2.name IN
(
SELECT elements.name
FROM elements INNER JOIN data ON elements.id=data.ownerid
WHERE data.typeid=10 AND data.valuer=1
)
GROUP BY elt1.name;
_______________________________________________________
elt1 are programs and elt 2 are classes in an 'elements' table linked in 'elementlinks' table. The data is the number of parents for a class. So this query counts for each program the number of classes having one parent. It returns only programs containing at least one class with one parent, but i'd like to have too programs not concerned.

Actually, i'd like to have in my query all programs listed, those not concerned may have a 0 for number of concerned classes. But this query doesn't match, it displays

PROGRAMS NB_OF _CLASSES_WITH_ONE_PARENT
elt11 1
elt13 3
elt14 2
elt16 1

AND i want:

PROGRAMS NB_OF _CLASSES_WITH_ONE_PARENT
elt11 1
elt12 0
elt13 3
elt14 2
elt15 0
elt16 1

If any ideas, thanx a lot.

 

[Kalin]

Would using a union work ??

Not quite sure bout the syntax but it should be something like:

SELECT elt1.name, COUNT(elt2.name)
FROM (elementlinks AS el_link INNER JOIN elements AS elt1 ON el_link.n1=elt1.id)
INNER JOIN elements AS elt2 ON el_link.n2=elt2.id
WHERE elt1.typeid=2 AND elt2.name IN
(
SELECT elements.name
FROM elements INNER JOIN data ON elements.id=data.ownerid
WHERE data.typeid=10 AND data.valuer=1
)
UNION
SELECT elt1.name, 0
FROM (elementlinks AS el_link INNER JOIN elements AS elt1 ON el_link.n1=elt1.id)
INNER JOIN elements AS elt2 ON el_link.n2=elt2.id
WHERE elt1.typeid=2 AND elt2.name NOT IN
(
SELECT elements.name
FROM elements INNER JOIN data ON elements.id=data.ownerid
WHERE data.typeid=10 AND data.valuer=1
)
GROUP BY elt1.name;

You probably need to tweak the names etc.

Greetz,

Kalin
Grtz,

Kalin

 

[tlbroadbent]

You could use a query similar to this. NOTE: The NZ function replaces null values with zero in the query below.

SELECT q1.name, NZ(q2.Cnt,0) As NameCnt
FROM
(SELECT Distinct Name FROM Elements) As q1
LEFT JOIN
(SELECT elt1.name, COUNT(elt2.name) As Cnt
FROM (elementlinks AS el_link
INNER JOIN elements AS elt1 ON el_link.n1=elt1.id)
INNER JOIN elements AS elt2 ON el_link.n2=elt2.id

WHERE elt1.typeid=2 AND elt2.name IN
(
SELECT elements.name
FROM elements
INNER JOIN data ON elements.id=data.ownerid
WHERE data.typeid=10 AND data.valuer=1
)
GROUP BY elt1.name) As q2
ON q1.name=q2.name;
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[pondi]

OK tl, i thought your query would work but in fact I have a FROM error on Access. I've simplified the query in order to point the trouble and i still have a FROM error on this simple query:
SELECT name
FROM
(SELECT name FROM elements)

Are you sure your syntax is correcr with Access??
Thanx a lot.

 

[tlbroadbent]

Did you notice the As q1 and AS q2 following the queries in parentheses? The aliases are essential. The following query will work.

SELECT name
FROM
(SELECT name FROM elements) As q1

The query I posted works in Access 2000. However, Access changes the parentheses to brackets and adds a period when I save the query. So you can make those changes yourself and try the following.

SELECT q1.name, NZ(q2.Cnt,0) As NameCnt
FROM
[SELECT Distinct Name FROM Elements]. As q1
LEFT JOIN
[SELECT elt1.name, COUNT(elt2.name) As Cnt
FROM (elementlinks AS el_link
INNER JOIN elements AS elt1 ON el_link.n1=elt1.id)
INNER JOIN elements AS elt2 ON el_link.n2=elt2.id

WHERE elt1.typeid=2 AND elt2.name IN
(
SELECT elements.name
FROM elements
INNER JOIN data ON elements.id=data.ownerid
WHERE data.typeid=10 AND data.valuer=1
)
GROUP BY elt1.name]. As q2
ON q1.name=q2.name; Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[pondi]

OK it seems to work with brackets, i had tried with aliases but no way.
Thanx for the tip!