CODEWITHSUNDEEP
oracle-database
defekas17
defekas17

Reputation: 171

query that get all results (including when condition not satisfied)

I'm trying to get a count with these query. I would like that it shows me all nameHost (althought the count was 0) but with these query, . Without where clause I don't have any problem but with where clause don't shows me the nameHost g.Could you help me please?

Example of tables:

nodes         
=======
nameHost        nodeid
---------       -------
a               a
b               b
b               f
e               e
g               g


jobsDefinition
================
node_id        job_name      app
----------     -----------   ---
a               fruit        one
b               apple        two
c               iron         three
a               banana       four
f               orange       four
g               gold         five

The output would be:

a 2 (fruit,banana)
b 2 (apple,orange)
e 0
***g 0 --> **this record not show me*****

These is my code

  SELECT n.namehost,
         COUNT (jd.node_id) AS Cnt,
         LISTAGG (jd.job_name, ',') WITHIN GROUP (ORDER BY 1) JB_NM
    FROM nodes n 
    LEFT JOIN jobdef jd 
    ON n.nodeid = jd.node_id
--sentence where
WHERE APP NOT LIKE 'five' 
GROUP BY n.namehost
ORDER BY namehost;

Thanks and sorry for my English!

Upvotes: 0

Views: 73

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Use the condition in the ON clause instead of where.

  SELECT n.namehost,
         COUNT (jd.node_id) AS Cnt,
         LISTAGG (jd.job_name, ',') WITHIN GROUP (ORDER BY 1) JB_NM
    FROM nodes n 
    LEFT JOIN jobdef jd 
    ON n.nodeid = jd.node_id
AND APP NOT LIKE 'five' 
GROUP BY n.namehost
ORDER BY namehost;

Upvotes: 1

Related Questions