Re: sql problem
How about this:
select unique t2.code, t1.name1
from table1 t1, table2 t2
where t2.name1 = t1.name1
union
select unique t2.code, t1.name2
from table1 t1, table2 t2
where t2.name1 = t1.name2
Tom
mukta mohindra wrote:
>
> I have the following scenario
>
> Table1
> (Name1, Name2) - Name2 can be NULL but not Name1
>
> Table2
> (Code, Name1)
> (Code, Name2).......
>
> I have to create a report associating a common code with a combination
> of Name1 and
> Name2. There can be many codes associated with Name1 or Name2
> (Code, Name1)
> (Code, Name2)
>
> This is what I came up with
>
> select code, name1, name2
> from tab1, tab2
> where (tab1.name1 = tab2.name2 or tab1.name2 = tab2.name2)
> and (tab2.name2 is null or
> tab2.code = (select code from tab2 A2 where (tab2.name2 = A2.name1 or> tab2.name2 =
> A2.name2)))
>
> Any ideas ????
>
> Thanks
> Mukta