Re: Need to return records from 2 file members

   Duplicate column names across multiple files, referenced in the same 
SQL statement may require removing the ambiguity so the query engine 
knows what is really wanted; i.e. the engine should not have to infer 
what is wanted, because that would easily have /a/ [to be clear, not 
*the* ] wrong result generated.

   The given statement has what is probably a bigger problem [again, for 
failure to tell the engine what is really wanted; but in this case the 
SQL standard has it making, what IMO is a *horrible* inference], that 
when the join is undefined a cross-product join will be performed.  Then 
not only is the name NAME ambiguous, but since there the join is 
undefined, the WHERE is ambiguous and unable to pare down the results.

   Anyhow, the given statement can have the ambiguity resolved with 
correlation identifiers; e.g. with M1 and M2 as identifiers:

     select *
     from lib/file M1, lib/alias_to_file_member M2
     where M1.NAME in (select NAME from lib2/file2)

   What was suggested however, requires the following; still leaving the 
join undefined, which if it was defined, might not require the addition 
of the ANDed selection:

     select * from lib/file M1, lib/alias_to_file_member M2
     where M1.NAME in (select NAME from lib2/file2)
       and M2.NAME in (select NAME from lib2/file2)

   Presumably what is really desired, is more likely to be:

     select *
      from         lib/file M1
      inner join   lib/alias_to_file_member M2
        on   M1.NAME = M2.NAME
        and  M1.NAME in (select NAME from lib2/file2)

   I tend to clarify my select list with the correlation IDs, and add 
correlation IDs to the sub-selects [although not required; it helps me 
keep track, and allows copy/paste into another statement where they 
might be required] as well:

     select M1.*, M2.*
      from         lib/file  M1
      inner join   lib/alias_to_file_member  M2
        on   M1.NAME = M2.NAME
        and  M1.NAME in (select F.NAME from lib2/file2  F)

Regards, Chuck
-- 
  All comments provided "as is" with no warranties of any kind 
whatsoever and may not represent positions, strategies, nor views of my 
employer

pgrimard@gmail.com wrote:
> I need to return records from 2 members of a file.  This file has
> a column that I'll call NAME for this example.  I have another
> file with a different structure that contains a similar NAME column
> which includes values that can be found in the NAME column of the
> first file.
> 
> I need to select records from 2 members in file 1, where the value
> of NAME is found in the result set of a select statement for the
> NAME column in file 2.
> 
> I'm using *sys naming, so slashes instead of dots, if that makes
> any difference.  I know my syntax is off.  I've tried something
> like the following, but I get an error saying NAME is ambiguous
> in my where clause.  I'm really just not sure what the syntax
> needs to be.
> 
>   select * from lib/file, lib/alias_to_file_member
>   where NAME in select NAME from lib2/file2
> 

I need to return records from 2 file members.  This file has a column that I'll
call NAME for this example.  I have another file with a different structure that
contains a similar NAME column which includes values that can be found in the
NAME column of the first file.<br />
<br />
I need to select records from 2 members in file 1, where the value of NAME is
found in the result set of a select statement for the NAME column in file
2.<br />
<br />
I'm using *sys naming, so slashes instead of dots, if that makes any difference.
 I know my syntax is off.  I've tried something like the following, but I get an
error saying NAME is ambiguous in my where clause.  I'm really just not sure
what the syntax needs to be.<br />
<br />
select * from lib/file, lib/alias_to_file_member<br />
<br />
where NAME in (<br />
  select NAME from lib2/file2<br />
)<br />
<br />

Ok, took some time to figure it out, but this is what I came up with and it
seems to work.  Not sure if there is a better way to do this, if there is I'm
open to suggestions.<br />
<br />
select * from lib/file<br />
<br />
where NAME in ( select NAME from lib2/file2 )<br />
<br />
union all<br />
<br />
select * from lib/file_member_alias<br />
<br />
where NAME in ( select NAME from lib2/file2 )<br />
<br />

   To get a UNION of the rows from the two file.members, versus joining, 
then that UNION ALL query should be what is required.
   I am not positive, but I believe that query could be rewritten [by 
the database query engine] as:

     select M1.*
      from         lib/file M1
      inner join   lib2/file2 F
        on   M1.NAME = F.NAME
    union all
     select M2.*
      from         lib/file_member_alias M2
      inner join   lib2/file2 F
        on   M2.NAME = F.NAME
    order by NAME asc

Regards, Chuck
-- 
  All comments provided "as is" with no warranties of any kind 
whatsoever and may not represent positions, strategies, nor views of my 
employer

pgrimard@gmail.com wrote:
> Ok, took some time to figure it out, but this is what I came up with
> and it seems to work.  Not sure if there is a better way to do this,
> if there is I'm open to suggestions.
>
>    select * from lib/file
>    where NAME in ( select NAME from lib2/file2 )
>   union all
>    select * from lib/file_member_alias
>    where NAME in ( select NAME from lib2/file2 )
>   order by NAME asc
>
> pgrimard@gmail.com wrote:
>> I need to return records from 2 members of a file.  This file has
>> a column that I'll call NAME for this example.  I have another
>> file with a different structure that contains a similar NAME column
>> which includes values that can be found in the NAME column of the
>> first file.
>>
>> I need to select records from 2 members in file 1, where the value
>> of NAME is found in the result set of a select statement for the
>> NAME column in file 2.
>>
>> I'm using *sys naming, so slashes instead of dots, if that makes
>> any difference.  I know my syntax is off.  I've tried something
>> like the following, but I get an error saying NAME is ambiguous
>> in my where clause.  I'm really just not sure what the syntax
>> needs to be.
>>
>>   select * from lib/file, lib/alias_to_file_member
>>   where NAME in select NAME from lib2/file2
>>