I just tried this, with some modifications (added the DISTINCT keyword and the FILESPACE_NAME column):
select distinct b.filespace_name, b.hl_name, b.ll_name from backups b where b.node_name='MYNODE' and b.state=inactive_version and (b.filespace_name || b.hl_name || b.ll_name) not in (select a.filespace_name || a.hl_name || a.ll_name from backups a where a.node_name='MYNODE' and a.state=active_version) It worked for me. But I used a very small test case, so maybe my test was not entirely comprehensive. This was run on a 5.1.0.0 TSM server. Regards, Andy Andy Raibeck IBM Software Group Tivoli Storage Manager Client Development Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS Internet e-mail: [EMAIL PROTECTED] (change eye to i to reply) The only dumb question is the one that goes unasked. The command line is your friend. "Good enough" is the enemy of excellence. "Seay, Paul" <[EMAIL PROTECTED]> Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]> 09/20/2002 14:19 Please respond to "ADSM: Dist Stor Manager" To: [EMAIL PROTECTED] cc: Subject: Re: SQL - punting for help It is probably not going to happen with the current database performance, but this would normally accomplish the task: select b.hl_name, b.ll_name from backups b where b.node_name='your node' and b.state=inactive_version and (b.hl_name || b.ll_name) not in (select a.hl_name || a.ll_name from backups a where a.node_name='your node' and a.state=active_version) I tried it on a single node and it did not find what I was looking for. I am going to turn in a problem record on it because it should work. Paul D. Seay, Jr. Technical Specialist Naptheon Inc. 757-688-8180 -----Original Message----- From: Williams, Tim P {PBSG} [mailto:[EMAIL PROTECTED]] Sent: Friday, September 20, 2002 1:59 PM To: [EMAIL PROTECTED] Subject: SQL - punting for help Ok. This one is teasing me...puzzeling... trying to get, from the backups table files that ONLY are inactive (if the file has an active copy, I don't want to have this in the output). have tried...myself... anyone? have tried state<>state, but this doesn't work.... ? Thanks tim