Here's what I finally came up with:

select count(*) as "Number of Free Drives" from drives -
where drive_name not in  -
  (select destination_name from paths where online='NO') and -
online='YES' and -
drive_state in ('EMPTY','UNKNOWN')

David

>>> deehre01 AT LOUISVILLE DOT EDU 08/01/03 11:01AM >>>
That almost gives me what I want.  But it counts drives once its
apprearance in the drives table and once for each occurance in the
paths
table (each drive appears multiples times in paths because they are
defines to storage agents).  So with all 8 dirves empty, it gives me
48
instead of 8.

David

>>> Richard.Foster AT HYDRO DOT COM 08/01/03 09:45AM >>>

> In tsm v5 where path and drive status are stored seperately, can
> anyone help with a sql query that would return the number of
> drives that are EMPTY and where the drive is online and the path
> to the drive is online.

Try

select count(*) from drives, paths where drives.online='YES' and
paths.online='YES' and drives.DRIVE_NAME=paths.DESTINATION_NAME and
drives.drive_state='EMPTY'