question

nikhil.kadam49521 avatar image
nikhil.kadam49521 asked

My query on Left joins?

--I am working on SQL SERVER 2005 WITH compatibility_level AS 80,I have a query on Left join --Take below 3 tables AND INSERT DATA CREATE TABLE #PM ( PMid int identity(1,1), PMName VARCHAR(100) ) CREATE TABLE #APM ( APMid int identity(100,1), APMName VARCHAR(100), PMid INT ) CREATE TABLE #TL ( TLid int identity(1000,1), TLName VARCHAR(100), APMid INT ) INSERT INTO #PM(PMName) SELECT 'PM1' UNION ALL SELECT 'PM2' UNION ALL SELECT 'PM3' UNION ALL SELECT 'PM4' UNION ALL SELECT 'PM5' UNION ALL SELECT 'PM6' INSERT INTO #APM(APMName,PMid) SELECT 'APM1',1 UNION ALL SELECT 'APM2',2 UNION ALL SELECT 'APM3',3 UNION ALL SELECT 'APM4',4 INSERT INTO #TL(TLName,APMid) SELECT 'TL1',100 UNION ALL SELECT 'TL2',200 --Am trying to get data for all project managers (including assigned apm and TL) --Below query1 giving result,but query2 throwing error like 'Query contains an outer-join request that is not permitted'.Why? --query1 SELECT * FROM #PM LEFT JOIN #APM ON #PM.PMid=#APM.PMid LEFT JOIN #TL ON #APM.APMid=#TL.APMid --query2 SELECT * FROM #PM,#APM,#TL WHERE #PM.PMid*=#APM.PMid AND #APM.APMid*=#TL.APMid --Am tring to add Main parent (PM Table) column PMID in to TL table.and updating corresponding PMID. ALTER TABLE #TL ADD PMID INT UPDATE #TL SET #TL.PMID=#PM.PMID FROM #PM,#APM,#TL WHERE #PM.PMid=#APM.PMid AND #APM.APMid=#TL.APMid --When i modify query2,by using new column PMid with Parent table PM's PMID its working why? SELECT * FROM #PM,#APM,#TL WHERE #PM.PMid*=#APM.PMid AND #PM.PMid*=#TL.PMid --Is this type of joins work only when parent table include in left joins? --Each table should mandatory to join with main parent table ? DROP TABLE #PM DROP TABLE #APM DROP TABLE #TL
sql-server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Though SQL Server 2005 allows for SQL Server 2000 compatibility level, the query plan generation has changed between the versions so much that some of the old style outer joins won't work anymore, specifically in this case where you have nested outer joins between more than two tables. ANSI joins has been the new way of writing queries since 1992. I think now (if not before) is really the time to drop the ansi-89-style join syntax. Deprecating the old-style joins was done for a very good reason. With all joins, the old-style syntax makes the code hard to read, and with outer joins in combination with filter-conditions in the WHERE clause it produces sometimes unpredictable results. And please remember that SQL Server 2005 has already left mainstream support and is out of extended support from april 2016. Now (if not before) is the time to upgrade and then you won't be able to use the old-style syntax at all for outer joins.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.