I think what you're looking for is an outer join:
select a.node_name, count(*)
from backups a
right join nodes b on a.node_name=b.node_name and a.node_name like '%-ORC'
where
((days(current_date) - days(backup_date) >= 30))
and a.state='ACTIVE_VERSION'
and b.contact like '%Oracle%'
group by a.node_name
On Tue, Apr 07, 2020 at 09:08:58AM +0000, Loon, Eric van (ITOP NS) - KLM wrote:
> Hi guys,
>
> It must be something very easy, but I can't seem find the solution myself...
> This is the query I use to list the total amount of Oracle backup files older
> than 30 days:
>
> select count(*) as OBSOLETE_BACKUPS from backups a,nodes b where
> ((days(current_date) - days(backup_date) >= 30)) and a.node_name like '%-ORC'
> and a.state='ACTIVE_VERSION' and a.node_name=b.node_name and b.contact like
> '%Oracle%'
>
> I also use this statement to list the files per node:
>
> select a.node_name, count(*) from backups a,nodes b where
> ((days(current_date) - days(backup_date) >= 30)) and a.node_name like '%-ORC'
> and a.state='ACTIVE_VERSION' and a.node_name=b.node_name and b.contact like
> '%Oracle%' group by a.node_name
>
> The statement works fine, but it only shows the nodes with an amount of files
> > 0. I'm looking for the same command which shows all %-ORC nodes. So when
> the amount is 0, it should display the node_name along with the value 0. I
> can't figure out how to accomplish it. :(
> Thanks for any help in advance!
>
> Kind regards,
> Eric van Loon
> Air France/KLM Storage & Backup
> ********************************************************
> For information, services and offers, please visit our web site:
> http://www.klm.com. This e-mail and any attachment may contain confidential
> and privileged material intended for the addressee only. If you are not the
> addressee, you are notified that no part of the e-mail or any attachment may
> be disclosed, copied or distributed, and that any other action related to
> this e-mail or attachment is strictly prohibited, and may be unlawful. If you
> have received this e-mail by error, please notify the sender immediately by
> return e-mail, and delete this message.
>
> Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its
> employees shall not be liable for the incorrect or incomplete transmission of
> this e-mail or any attachments, nor responsible for any delay in receipt.
> Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch
> Airlines) is registered in Amstelveen, The Netherlands, with registered
> number 33014286
> ********************************************************
--
-- Skylar Thompson (skyl...@u.washington.edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine
