SQLTeam.com | Weblogs | Forums

Changing query

sql2012

#1

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]
AS

SELECT c.Lab_Sample_ID
,c.Client_Sample_ID
,x.SampleLocationNo
,x.Sample_ID as SampleSites_SampleId
,[DilutionFactor]
,[CollectionDate]
,[AnalysisMethod]
,[CAS]
,[Analyte]
,[Result]
,[LowLimit]
,[Unit]
,[Flag]
,c.ObjectID
,c.FilePathId
,x.FilePathId as sampleFilePathID
,x.Lab_Sample_ID as sampleLab_Sample_ID
,x.Client_Sample_ID as sampleClient_Sample_ID
,[QAQCType]
,[Zone]
,[Matrix]
,[DataReported]
,[ReportWhereData]
,[DepthFromFtBGS]
,[DepthToFtBGS]
,x.Shape

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)


#2

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)