SELECT COUNT(*) as x,

Hur gör man i en "SELECT COUNT(*) as x" när man vill få visa "0" om den inte hittar några annars ska den visa hur många träffar som man får.

Antar man måste göra nån sorts if sats men det står still just. Nån som några förslag eller exempel?

SELECT COUNT(*) borde ge dig 0 om det inte finns några poster som motsvarar dina kriterier. Gör den inte det?

grejen är den att jag använder det som kolumner som jag sedan matchar mot varandra.

SELECT A.Reviewer_Id,A.User_Name,A.Password,A.Name,A.Reviewer_EMail,
ab.NumberAbstracts,s.NumberScored,s.client_id,s.project_id,ab.client_id,ab.project_id 
FROM Reviewers A ,

(SELECT COUNT(Abstract_Id) as NumberAbstracts, apr.client_id,apr.project_id,apr.reviewer_id 
from AbstractsPerReviewer apr, Reviewers A2 where (apr.client_id = a2.client_id) and 
(apr.project_id = a2.project_id) and (apr.reviewer_id = a2.user_name or apr.reviewer_id = a2.reviewer_id

or not exists(SELECT  *
from Reviewers A2 where 
(apr.reviewer_id = a2.user_name or apr.reviewer_id = a2.reviewer_id)))

GROUP BY apr.client_id, apr.project_id, apr.reviewer_id ) ab, 

(SELECT COUNT(Abstract_Id) as NumberScored, sc.client_id, Sc.project_id,Sc.reviewer_id 
FROM Scores sc, Reviewers A3 where Sc.client_id = a3.client_id and Sc.project_id = a3.project_id and 
(Sc.reviewer_id = a3.user_name or Sc.reviewer_id = a3.reviewer_id

or not exists(SELECT  *
from Reviewers where 
(Sc.reviewer_id = Reviewers.user_name or Sc.reviewer_id = Reviewers.reviewer_id)))

GROUP BY sc.client_id, Sc.project_id, Sc.reviewer_id) s 

where A.Client_Id = 'SHOCKLOGIC' AND A.Project_Id = 'DEMO0001' and s.Client_Id = a.Client_Id AND 
s.project_Id = a.project_id and ab.Client_Id = a.Client_Id AND ab.project_Id = a.project_id and 
ab.reviewer_Id = a.user_name AND ab.reviewer_id = s.reviewer_id AND 
ab.NumberAbstracts > s.NumberScored 

GROUP BY A.Reviewer_Id,A.User_Name,A.Password,A.Name,A.Reviewer_EMail,ab.NumberAbstracts,s.NumberScored,
s.client_id,s.project_id,ab.client_id,ab.project_id ORDER BY A.Reviewer_Id DESC

Använd outer join.

Hur menar du då? nåt exempel?

Du skriver

from t , (select ... ) s where t.c = s.c

istället

from t left join (select ... ) s on t.c = s.c

då får du ett resultat även om det inte finns matchande rader i s

det verkar inte riktigt gå att skriva så med den queryn jag har. Jag får syntax fel. Har du nåt fulllbordat exemel eller kan du eller ser du vart jag ska ändra?

Det jag menar är

SELECT A.Reviewer_Id,A.User_Name,A.Password,A.Name,A.Reviewer_EMail  ,
ab.NumberAbstracts,s.NumberScored,s.client_id,s.project_id,a  b.client_id,ab.project_id 
FROM Reviewers A left join
(SELECT COUNT(Abstract_Id) as NumberAbstracts, apr.client_id,apr.project_id,apr.reviewer_id 
from AbstractsPerReviewer apr, Reviewers A2 where (apr.client_id = a2.client_id) and 
(apr.project_id = a2.project_id) and (apr.reviewer_id = a2.user_name or apr.reviewer_id = a2.reviewer_id
or not exists(SELECT  *
from Reviewers A2 where 
(apr.reviewer_id = a2.user_name or apr.reviewer_id = a2.reviewer_id)))
GROUP BY apr.client_id, apr.project_id, apr.reviewer_id ) ab 
on ab.project_Id = a.project_id
and ab.Client_Id = a.Client_Id 
and ab.reviewer_Id = a.user_name left join
(SELECT COUNT(Abstract_Id) as NumberScored, sc.client_id, Sc.project_id,Sc.reviewer_id 
FROM Scores sc, Reviewers A3 where Sc.client_id = a3.client_id and Sc.project_id = a3.project_id and 
(Sc.reviewer_id = a3.user_name or Sc.reviewer_id = a3.reviewer_id
or not exists(SELECT  *
from Reviewers where 
(Sc.reviewer_id = Reviewers.user_name or Sc.reviewer_id = Reviewers.reviewer_id)))
GROUP BY sc.client_id, Sc.project_id, Sc.reviewer_id) s 
on s.Client_Id = a.Client_Id
and s.project_Id = a.project_id
and ab.NumberAbstracts > s.NumberScored 
where A.Client_Id = 'SHOCKLOGIC' 
  AND A.Project_Id = 'DEMO0001' 
GROUP BY A.Reviewer_Id,
         A.User_Name,A.Password,
         A.Name,
         A.Reviewer_EMail  ,
         ab.NumberAbstracts,
         s.NumberScored,
         s.client_id,
         s.project_id,
         ab.client_id,
         ab.project_id 
ORDER BY A.Reviewer_Id DESC

Sen så har jag inte sportmössa att avgöra om det är vad du vill ha.

Tack det löste sig