>Hi Sergey,
>
>It worked like a charm. Thanks a million.
>
>Here is the ASA code in case someone is interested.
>
>

>create procedure dba.sel_contacts(
>	in @namePK integer)
>
>begin
>
>  select
>  	number(*) as sqn
>	  into #seqenceNo
>  from names na
>  key left join address ad
>  key left join phonenumber ph
>  key left join email
>  where na.namepk = @namePK ;
>
>  select
>    ad.addresspk,
>    ad.xlinkfk,
>    ad.typefk,
>    ad.descrp,
>    ad.address1,
>    ad.address2,
>    ad.address3,
>    ad.city,
>    ad.state,
>    ad.zipcode,
>    ad.country,
>    ad.namefk,
>    number(*) as sqn
>  	into #add
>  from address ad
>  where ad.namefk = @namePK
>    and ad.delflag = 0 ;
>
>	select
>    ph.phonenumberpk,
>    ph.xlinkfk,
>    ph.typefk,
>    ph.descrp,
>    ph.prefix,
>    ph.phonenumber,
>    ph.extension,
>    ph.delflag,
>    ph.namefk,
>	  number(*) as sqn
>    into #phn
>  from phonenumber ph
>  where ph.namefk = @namePK
>    and ph.delflag = 0 ;
>
>	select
>  	em.emailaddresspk,
>    em.xlinkfk,
>    em.typefk,
>    em.descrp,
>    em.email,
>    em.namefk,
>	  number(*) as sqn
>    into #emm
>  from email em
>  where em.namefk = @namePK
>  	and em.delflag = 0 ;
>
>	select * from
>	(	select
>      na.namepk,
>      na.loginuser,
>      na.typefk,
>      na.firstname,
>      na.lastname,
>      na.title,
>      na.prefix,
>      na.ownerfk,
>      sq.sqn
>   	from names na
>  	cross join #seqenceNo sq
>    where na.namePK = @namePK) as naa
>	left join (
>  	select * from #add
>		) as ad
>    on ad.sqn = naa.sqn
>	left join (
>  	select * from #phn
>		) phh
>    on phh.sqn = naa.sqn
>	left join (
>  	select * from #emm
>		) as emm
>   	on emm.sqn = naa.sqn
>	WHERE COALESCE(ad.NameFK, emm.NameFK, phh.NameFK) IS NOT NULL ;
>end;
>
>

>
Hi James,

I'm glad it worked for you and I like the way you change the first SELECT INTO. I still have nagging feeling that there's simpler way to get the same result. Anyway, as Russian saying goes, the small bird in the hand is better than big bird in the sky. :)