| SELECT COUNT [message #20689] |
Fri, 14 June 2002 00:28  |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
Hi! I had an SQL question... it has been killing me for the past 2 days now and I just can't find the solution. I'm working on a french database, so the variable names are in french... anyway, here we go :
Poste = Workstation
Visite = Medical Checkup
Num stands for Number like Index (not Number of!)
SELECT O.O_Poste, COUNT (V.Num_Visite) AS DouleurAct
FROM POSTE AS O, VISITE AS V, PATIENT AS P
WHERE V.V_NumeroP = P.P_Numero
AND P.P_NumPoste = O.O_NumPoste
AND (V.V_Epaule1 > "0"
OR V.V_Coude1 > "0"
OR V.V_AVBras1 > "0"
OR V.V_Bras1 > "0"
OR V.V_Main1 > "0"
OR V.V_Poignet1 > "0"
OR V.V_Doigt1 > "0"
OR V.V_Dos1 > "0"
OR V.V_Nuque1 > 0 )
AND (V.V_Cause = "1" OR V.V_Cause="10000")
AND P.P_Numero NOT IN (SELECT P_Numero FROM Rq_Graph1_UnionPersonnel)
GROUP BY O.O_Poste
ORDER BY O.O_Poste;
A patient can have : recent pains, pains from 12 months ago, or both. All these cases are mutually exclusive. Now, this query is supposed to return the number of people who have recent pains. Oh, and also the reason for the checkup has to be '1' or '10000' which have already been defined before.
My problem is the following :
This query counts perfectly and returns the exact values that I was looking for BUT it does not return a value when the COUNT = 0. In other words it doesn't display ALL of the rows from the POSTE table.Therefore I don't have all of the rows from the POSTE (Workstation) table and I only get the rows where COUNT > 0. How could I make changes for displaying all of the rows in the POSTE table including the ones that have 0 as the result of COUNT?
|
|
|
|
| Re: SELECT COUNT [message #20696 is a reply to message #20689] |
Fri, 14 June 2002 06:34  |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
Well, I think your problem is that you need to do outer joins to table "V" and you want to make the counting an inline view. Maybe I don't understand because of the French thing. Anyway, here I have two tables, and I want a count of rows in the second table that have the key = the key of the first table, and I want to see zero if there is no row in the second table matching the first. If that roughly matches your problem, the following should be helpful:
10:17:32 ==> select * from table1;
KEY1 LAST_NAME
--------- --------------------
3 Bichel
1 John
2 Mike
4 Thomas
10:19:07 ==> select * from table2;
KEY2 FIRST_NAME
--------- --------------------
1 Joe
1 Gray
2 Tyson
1 Greme
2 Anthony
3 Andy
6 rows selected.
10:19:12 ==> select key1, nvl(cnt,0)
10:21:11 2 from table1,
10:21:11 3 (select key2, count(*) cnt from table2 group by key2)
10:21:11 4 where key1 = key2(+);
KEY1 NVL(CNT,0)
--------- ----------
1 3
2 2
3 1
4 0
10:21:14 ==>
|
|
|
|
| Re: SELECT COUNT [message #20715 is a reply to message #20689] |
Fri, 14 June 2002 19:13 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
|
Because you are checking the P.P_Numero against V.V_NumeroP linking to other conditions by AND logical operator. So when there is no row in any of the tables, there is no question of equality check for P.P_Numero with V.V_Numero, hence the condition would not qualify for further check/process and it skips to next. That is, where there is no row/value to check (if count=0) how can it return true and give you the value right? No value no check no true result. So by the result, you dont see any records that you are expecting it would return in the output when the row count is zero. Hope you understand my point. Check it out. Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
