SQL Help (Join)

[Andrew21210]

Hello. I have the following select statement:

SELECT cast((n.node_name) as char(50)) as NODE , cast((n.platform_name) as char(20)) as PLATFORM , cast((n.contact) as char(50)) as CONTACT, cast((TRIM(CHAR(client_version))||'.'||TRIM(CHAR(client_release))||'.'||TRIM(CHAR(client_level))||'-'||TRIM(CHAR(client_sublevel))) as char(13)) as TSM_VERSION, cast(float(a.total_mb)/1024/1024 as DEC(8,2)) AS TOTAL_GB FROM nodes n, auditocc a WHERE n.node_name=a.node_name and n.contact like '%CIIDS%' ORDER BY 2 ASC

It works fine but I would also like to have it display the schedule_name which will need to be pulled from another table using a join. I have tried a few different things but I am not very good with joins. Could anyone assist me?

 

[moon-buddy]

Try this

SELECT cast((n.node_name) as char(50)) as NODE,cast((n.platform_name) as char(20)) as PLATFORM,cast((n.contact) as char(50)) as CONTACT,cast((TRIM(CHAR(client_version))||'.'||TRIM(CHAR(client_release))||'.'||TRIM(CHAR(client_level))||'-'||TRIM(CHAR(client_sublevel))) as char(13)) as TSM_VERSION,cast(float(a.total_mb)/1024/1024 as DEC(8,2)) AS TOTAL_GB,assoc.schedule_name as Schedule FROM nodes n,auditocc a,associations assoc WHERE n.node_name=a.node_name and n.node_name=assoc.node_name and n.contact like '%CIIDS%' ORDER BY 2 ASC

 

[Andrew21210]

Try this

SELECT cast((n.node_name) as char(50)) as NODE,cast((n.platform_name) as char(20)) as PLATFORM,cast((n.contact) as char(50)) as CONTACT,cast((TRIM(CHAR(client_version))||'.'||TRIM(CHAR(client_release))||'.'||TRIM(CHAR(client_level))||'-'||TRIM(CHAR(client_sublevel))) as char(13)) as TSM_VERSION,cast(float(a.total_mb)/1024/1024 as DEC(8,2)) AS TOTAL_GB,assoc.schedule_name as Schedule FROM nodes n,auditocc a,associations assoc WHERE n.node_name=a.node_name and n.node_name=assoc.node_name and n.contact like '%CIIDS%' ORDER BY 2 ASC

This worked. Many thanks. Another question. Is it possible to modify this select to include the amount of data backed up in the last 24 hours for each node that the above select outputs? Or is that asking too much of SQL?

 

[moon-buddy]

Here you go:

SELECT cast((n.node_name) as char(50)) as NODE,cast((n.platform_name) as char(20)) as PLATFORM,cast((n.contact) as char(50)) as CONTACT,cast((TRIM(CHAR(n.client_version))||'.'||TRIM(CHAR(n.client_release))||'.'||TRIM(CHAR(n.client_level))||'-'||TRIM(CHAR(n.client_sublevel))) as char(13)) as TSM_VERSION,cast(float(a.total_mb)/1024 as DEC(8,2)) AS TOTAL_GB,assoc.schedule_name as Schedule,summ.activity,CAST(FLOAT(SUM(summ.bytes))/1024/1024/1024 AS DECIMAL(8,2)) as GB_BACKUP FROM nodes n,auditocc a,associations assoc,summary summ WHERE (n.node_name=a.node_name and n.node_name=assoc.node_name and n.node_name=summ.entity) and summ.activity='BACKUP' and n.contact like '%CIIDS%' and DAYS(current_timestamp)-DAYS(summ.end_time)<=1 GROUP by n.node_name,n.platform_name,n.client_version,n.client_release,n.client_level,n.client_sublevel,n.contact,a.total_mb,assoc.schedule_name,summ.activity ORDER BY 2 ASC