SQLTeam.com | Weblogs | Forums

Changing query



Hi Friends,

In the query below I am creating view of three tables. What i had done is to first join two tables as One named as x and then joined the x table to The Third table. The query is fine. But due to some technical reasons I want to get the same result by join each table separately i.e when you clcik on design view three tables show show up. How can i achieve it.
Thank You

ALTER VIEW [test].[DataForSampleIDs_VW]

SELECT c.Lab_Sample_ID
,x.Sample_ID as SampleSites_SampleId
,x.FilePathId as sampleFilePathID
,x.Lab_Sample_ID as sampleLab_Sample_ID
,x.Client_Sample_ID as sampleClient_Sample_ID

FROM test.[dbo].[tblChemDataforGIS] c
left Join
( SELECT s.*,sp.Shape,sp.Sample_ID,sp.Sample_Type FROM
[test].[dbo].[tblSampleIDsforGIS] s
left join test.dbo.SAMPLESITES sp
ON sp.Sample_ID = S.SampleLocationNo
) x
ON C.Client_Sample_ID = x.Client_Sample_ID
and C.Lab_Sample_ID = x.Lab_Sample_ID
and isnull(c.FilePathId,0) =isnull(x.FilepathID,0)


I am not very well-versed with the design-view, so not sure what/how it would show up in there. Sounds like the join you are looking for is something like this.

FROM    test.[dbo].[tblChemDataforGIS] c
		LEFT JOIN [test].[dbo].[tblSampleIDsforGIS] s
			ON C.Client_Sample_ID = s.Client_Sample_ID
        LEFT JOIN 
			[test].[dbo].[tblSampleIDsforGIS] s
			LEFT JOIN test.dbo.SAMPLESITES sp ON sp.Sample_ID = S.SampleLocationNo
        )  ON 
			C.Client_Sample_ID = x.Client_Sample_ID
			AND C.Lab_Sample_ID = x.Lab_Sample_ID
			AND ISNULL(c.FilePathId, 0) = ISNULL(x.FilepathID, 0)