Wow. I am going to have to study on this for a while. Thanks Sergey.

>Hi James,
>
>I don't now an easy way to do it but following works in MS SQL 2000. It may give you some ideas.

>SET NOCOUNT ON
>
>DECLARE @NamePK int
>SET @NamePK = 2
>-- Create seq #'s from 1 to 16
>IF object_id('tempdb..#sn') IS NOT NULL
>	DROP TABLE #sn
>SELECT *
>INTO #sn
>FROM ( SELECT b1+b2+b3+b4+1 AS sqn FROM (SELECT 0 b1  UNION SELECT 1    b1)  t1
>CROSS JOIN (SELECT 0 b2  UNION SELECT 2    b2)  t2
>CROSS JOIN (SELECT 0 b3  UNION SELECT 4    b3)  t3
>CROSS JOIN (SELECT 0 b4  UNION SELECT 8    b4)  t4
>) dt1
>ORDER BY 1
>
>DECLARE @id int
>
>-- Add seq #'s
>IF object_id('tempdb..#adsn') IS NOT NULL
>	DROP TABLE #adsn
>SELECT *, CAST(0 AS int) AS sqn
>INTO #adsn
>FROM ( SELECT * FROM address ad
>			WHERE ad.nameFk = @NamePK
>		) dt1
>
>SET @id = 0
>UPDATE #adsn SET @id = sqn = @id + 1
>--SELECT * FROM #adsn
>
>-- Add seq #'s
>IF object_id('tempdb..#emsn') IS NOT NULL
>	DROP TABLE #emsn
>SELECT *, CAST(0 AS int) AS sqn
>INTO #emsn
>FROM ( SELECT * FROM Email em
>			WHERE em.nameFk = @NamePK
>		) dt1
>
>SET @id = 0
>UPDATE #emsn SET @id = sqn = @id + 1
>--SELECT * FROM #emsn
>
>-- Add seq #'s
>IF object_id('tempdb..#phsn') IS NOT NULL
>	DROP TABLE #phsn
>SELECT *, CAST(0 AS int) AS sqn
>INTO #phsn
>FROM ( SELECT * FROM Phonenumber ph
>			WHERE ph.nameFk = @NamePK
>		) dt1
>
>SET @id = 0
>UPDATE #phsn SET @id = sqn = @id + 1
>--SELECT * FROM #phsn
>
>-- Join on seq #'s
>SELECT *
>	FROM ( SELECT * FROM names
>				CROSS JOIN #sn
>				WHERE namePk = @NamePK
>			) na
>	LEFT JOIN #adsn ad ON ad.sqn = na.sqn
>	LEFT JOIN #emsn em ON em.sqn = na.sqn
>	LEFT JOIN #phsn ph ON ph.sqn = na.sqn
>	WHERE COALESCE(ad.NameFK, em.NameFK, ph.NameFK) IS NOT NULL
>

>
>
>>
>>I have a contacts system that has four tables, Names, address, phonenumber, email. The last three have a nameFK foreign key that joins with the names table. I designed it this way so I could have multiple entries of each time of contact info. I have two rows in address, three rows in phonenumber and two rows in email. I am trying to create a query that will give me three rows (rows in phonenumber). What I am getting is 18. Is there a way to do this or am I going to need to redesign the tables. I am using Syabase ASA database.
>>
>>Here is my Query. Thanks.
>>
>>

>>select  *
>>from   names na
>>left outer join address ad
>>on ad.namefk = na.namepk
>>left outer join email em
>>on em.namefk = na.namePK
>>left outer join phonenumber  ph
>>on ph.namefk = na.namePK
>>where  na.namepk= 1
>>