InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 09:44:57 GMT20http://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspxMine won't return rows in the Docs table if they don't have corresponding values in at least one of the sub-tables. That might explain part of the rowcount difference. Whether that's correct or not is something only the OP can answer. If it's not correct, it's easy to modify the code to add another join to that table.The I/O difference I originally noted was from one of the earlier queries, which did one scan per value in Docs, for each of the sub-tables. If that's been fixed, it's been fixed. But it wasn't noted till I tested for that, so I mentioned always testing I/O, that's all.Mon, 10 Sep 2012 06:05:57 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspx[quote][b]GSquared (9/7/2012)[/b][hr]Sorry for the delay in replying. Been recovering from surgery.Try this:Expand the number of documents beyond 5 up to some reasonable number, like 10,000 or 1-million. Add random GE, GR, and Ref entries for them. I'm not sure how the original requestor would want Docs entries without anything in any of the sub-tables, so leave that aspect alone for now.Cluster all the tables reasonably. I used DocNum and Seq on the sub-tables, and added a PK (clustered) on DocNum in Docs.Modify my Full Outer query as follows:[code="sql"]SELECT COALESCE(GR.DocNum, GE.DocNum, Ref.DocNum), GR, GR.Seq, GE, GE.Seq, COALESCE(Ref.Ref, R2.Ref), COALESCE(Ref.Seq, R2.Seq)FROM dbo.GR FULL OUTER JOIN dbo.GE ON GR.DocNum = GE.DocNum AND GR.Seq = GE.Seq FULL OUTER JOIN dbo.Ref ON GE.DocNum = Ref.DocNum AND GE.Seq = Ref.Seq FULL OUTER JOIN dbo.Ref AS R2 ON GR.DocNum = R2.DocNum AND GR.Seq = R2.Seq[/code]Test again. Include IO characteristics in your testing. Always. On loaded systems, CPU time is far less likely to be your main bottleneck, while I/O is far more likely to be key to the performance of the whole server. (Network latency and bandwidth can sometimes (often) trump I/O as a real bottleneck on overall performance, but that's not usually something you can do much about except reduce the size of datasets being shipped around.)Also, assigning variable values isn't a valid test. Many times, SQL Server will bypass running the intended performance-testing query, and just grab one row and use that for the variable value. It knows it only needs one value, so it only grabs one. I've seen this happen in SQL 2000 and 2005. Haven't tested in 2008 or 2012, but I think it's a fair assumption they haven't made the engine dumber about that kind of thing. Per discussions on this site, others have proven the same thing.If you want to test without returning a result-set, insert into temp tables. Best if you build the temp tables explicitly outside of the final query, instead of Select Into, so that DDL time doesn't add to the query execution artificially. Avoiding returning a dataset avoids skewing for network issues if you're querying remotely, and avoids rendering-time in the UI as a possible factor as well. I recommend getting the time and I/O stats out of a server-side trace, rather than Set Stats On, because Set Stats On has been proven to have potential impacts on actual query performance. Doesn't appear to affect this particular set of tests, but it's a good practice nonetheless.[/quote]Your query doesn't give correct results against the OPs original data - it returns 8 rows instead of 5.Mon, 10 Sep 2012 03:00:16 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspxIndeed Gus, the new version of your query seems to be fastest.[code="plain"]========== Mark ===========================================(20000 row(s) affected)Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#Ref___000000000052'. Scan count 2, logical reads 184, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#GE___000000000051'. Scan count 2, logical reads 130, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#GR___000000000050'. Scan count 2, logical reads 86, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 171 ms, elapsed time = 438 ms.========== GSquared ===========================================(30000 row(s) affected)Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#GE___000000000051'. Scan count 1, logical reads 65, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#GR___000000000050'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#Ref___000000000052'. Scan count 2, logical reads 184, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 93 ms, elapsed time = 457 ms.========== Dwain ===========================================Warning: Null value is eliminated by an aggregate or other SET operation.(45000 row(s) affected)Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#GR___000000000050'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#GE___000000000051'. Scan count 1, logical reads 65, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#Ref___000000000052'. Scan count 1, logical reads 92, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#Doc___00000000004F'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 156 ms, elapsed time = 668 ms.[/code]I am not seeing a large difference in IOs unless I am missing something. It is interesting that each of the 3 query is returning a different number of rows. Wonder which is right.Sun, 09 Sep 2012 18:39:12 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspx[quote][b]GSquared (9/7/2012)[/b][hr]Sorry for the delay in replying. Been recovering from surgery....[/quote]Get well soon, GGeezer.Fri, 07 Sep 2012 07:32:16 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspxSorry for the delay in replying. Been recovering from surgery.Try this:Expand the number of documents beyond 5 up to some reasonable number, like 10,000 or 1-million. Add random GE, GR, and Ref entries for them. I'm not sure how the original requestor would want Docs entries without anything in any of the sub-tables, so leave that aspect alone for now.Cluster all the tables reasonably. I used DocNum and Seq on the sub-tables, and added a PK (clustered) on DocNum in Docs.Modify my Full Outer query as follows:[code="sql"]SELECT COALESCE(GR.DocNum, GE.DocNum, Ref.DocNum), GR, GR.Seq, GE, GE.Seq, COALESCE(Ref.Ref, R2.Ref), COALESCE(Ref.Seq, R2.Seq)FROM dbo.GR FULL OUTER JOIN dbo.GE ON GR.DocNum = GE.DocNum AND GR.Seq = GE.Seq FULL OUTER JOIN dbo.Ref ON GE.DocNum = Ref.DocNum AND GE.Seq = Ref.Seq FULL OUTER JOIN dbo.Ref AS R2 ON GR.DocNum = R2.DocNum AND GR.Seq = R2.Seq[/code]Test again. Include IO characteristics in your testing. Always. On loaded systems, CPU time is far less likely to be your main bottleneck, while I/O is far more likely to be key to the performance of the whole server. (Network latency and bandwidth can sometimes (often) trump I/O as a real bottleneck on overall performance, but that's not usually something you can do much about except reduce the size of datasets being shipped around.)Also, assigning variable values isn't a valid test. Many times, SQL Server will bypass running the intended performance-testing query, and just grab one row and use that for the variable value. It knows it only needs one value, so it only grabs one. I've seen this happen in SQL 2000 and 2005. Haven't tested in 2008 or 2012, but I think it's a fair assumption they haven't made the engine dumber about that kind of thing. Per discussions on this site, others have proven the same thing.If you want to test without returning a result-set, insert into temp tables. Best if you build the temp tables explicitly outside of the final query, instead of Select Into, so that DDL time doesn't add to the query execution artificially. Avoiding returning a dataset avoids skewing for network issues if you're querying remotely, and avoids rendering-time in the UI as a possible factor as well. I recommend getting the time and I/O stats out of a server-side trace, rather than Set Stats On, because Set Stats On has been proven to have potential impacts on actual query performance. Doesn't appear to affect this particular set of tests, but it's a good practice nonetheless.Fri, 07 Sep 2012 07:22:19 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspx[quote][b]dwain.c (8/29/2012)[/b][hr]Gus,That's an interesting point on the IOs but in the below test harness (5 docs) it still runs pretty fast, although clearly not as fast as Mark's.[code="sql"]Create table #Doc (DocNum nvarchar(15) NOT NULL);Insert into #Doc (DocNum)Values ('1'),('2'),('3'),('4'),('5');------------------------------Create Table #GR (DocNum nvarchar(15) NOT NULL,GR nvarchar(15) NOT NULL,Seq int NOT NULL)--------------------------------Create Table #GE (DocNum nvarchar(15) NOT NULL,GE nvarchar(15) NOT NULL,Seq int NOT NULL)--------------------------------------------------Create Table #Ref (DocNum nvarchar(15) NOT NULL,Ref nvarchar(15) NOT NULL,Seq int NOT NULL);WITH Tally (number) AS ( SELECT TOP 2000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b)Insert into #GR(DocNum, GR, Seq)SELECT b.number,'GR_'+CAST(a.number AS VARCHAR(10)),a.numberFROM Tally aCROSS APPLY (SELECT number FROM Tally WHERE number BETWEEN 1 AND 5) b--FROM master.dbo.spt_values--WHERE number BETWEEN 1 AND 500 AND type='P';WITH Tally (number) AS ( SELECT TOP 3000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b)Insert into #GE(DocNum, GE, Seq)SELECT b.number,'GE_'+CAST(a.number AS VARCHAR(10)),a.numberFROM Tally aCROSS APPLY (SELECT number FROM Tally WHERE number BETWEEN 1 AND 5) b--FROM master.dbo.spt_values--WHERE number BETWEEN 1 AND 750 AND type='P';WITH Tally (number) AS ( SELECT TOP 4000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b)Insert into #Ref (DocNum, Ref, Seq)SELECT b.number,'Ref_'+CAST(a.number AS VARCHAR(10)),a.numberFROM Tally aCROSS APPLY (SELECT number FROM Tally WHERE number BETWEEN 1 AND 5) b--FROM master.dbo.spt_values--WHERE number BETWEEN 1 AND 1000 AND type='P';GOPRINT '========== Mark ==========================================='DECLARE @DocNum nvarchar(15)DECLARE @GR nvarchar(15)DECLARE @GE nvarchar(15)DECLARE @Ref nvarchar(15)DECLARE @Seq1 intDECLARE @Seq2 intDECLARE @Seq3 intSET STATISTICS TIME ON;WITH AllSeq AS ( SELECT Seq FROM #GR UNION SELECT Seq FROM #GE UNION SELECT Seq FROM #Ref)SELECT @DocNum = d.DocNum, @GR = gr.GR, @Seq1 = gr.Seq, @GE = ge.GE, @Seq2 = ge.Seq, @Ref = rf.Ref, @Seq3 = rf.SeqFROM #Doc dCROSS JOIN AllSeq sqLEFT OUTER JOIN #GR gr ON gr.DocNum = d.DocNum AND gr.Seq = sq.SeqLEFT OUTER JOIN #GE ge ON ge.DocNum = d.DocNum AND ge.Seq = sq.SeqLEFT OUTER JOIN #Ref rf ON rf.DocNum = d.DocNum AND rf.Seq = sq.Seq;SET STATISTICS TIME OFFGOPRINT '========== GSquared ==========================================='DECLARE @DocNum nvarchar(15)DECLARE @GR nvarchar(15)DECLARE @GE nvarchar(15)DECLARE @Ref nvarchar(15)DECLARE @Seq1 intDECLARE @Seq2 intDECLARE @Seq3 intSET STATISTICS TIME ONSELECT @DocNum = COALESCE(#GR.DocNum, #GE.DocNum, #Ref.DocNum), @GR = GR, @Seq1 = #GR.Seq, @GE = GE, @Seq2 = #GE.Seq, @Ref = Ref, @Seq3 = #Ref.SeqFROM #GR FULL OUTER JOIN #GE ON #GR.DocNum = #GE.DocNum AND #GR.Seq = #GE.Seq FULL OUTER JOIN #Ref ON #GE.DocNum = #Ref.DocNum AND #GE.Seq = #Ref.Seq OR #GR.DocNum = #Ref.DocNum AND #GR.Seq = #Ref.Seq;SET STATISTICS TIME OFFGOPRINT '========== Dwain ==========================================='DECLARE @DocNum nvarchar(15)DECLARE @GR nvarchar(15)DECLARE @GE nvarchar(15)DECLARE @Ref nvarchar(15)DECLARE @Seq1 intDECLARE @Seq2 intDECLARE @Seq3 intSET STATISTICS TIME ON;WITH CTE AS ( SELECT n=1, DocNum, Ref, Seq FROM #Ref UNION ALL SELECT 2, DocNum, GE, Seq FROM #GE UNION ALL SELECT 3, DocNum, GR, Seq FROM #GR)SELECT @DocNum=a.DocNum ,@GR=MAX(CASE n WHEN 3 THEN Ref END) ,@Seq1=MAX(CASE n WHEN 3 THEN Seq END) ,@GE=MAX(CASE n WHEN 2 THEN Ref END) ,@Seq2=MAX(CASE n WHEN 2 THEN Seq END) ,@Ref=MAX(CASE n WHEN 1 THEN Ref END) ,@Seq3=MAX(CASE n WHEN 1 THEN Seq END )FROM CTE aINNER JOIN #Doc b ON a.DocNum = b.DocNumGROUP BY a.DocNum, RefSET STATISTICS TIME OFFDROP TABLE #Doc, #Ref, #GR, #GE[/code]Results:[code="other"](10000 row(s) affected)(15000 row(s) affected)(20000 row(s) affected)========== Mark =========================================== SQL Server Execution Times: CPU time = 93 ms, elapsed time = 112 ms.========== GSquared =========================================== SQL Server Execution Times: CPU time = 177918 ms, elapsed time = 180257 ms.========== Dwain =========================================== SQL Server Execution Times: CPU time = 156 ms, elapsed time = 313 ms.[/code]Let me know if I did something wrong (always a possibility before I've had my morning coffee :-)).Interestingly, when I bumped the doc count to 40, Mark's SQL seemed to begin to benefit from parallizing the query (note elapsed ms < CPU ms). So I retried with OPTION (MAXDOP 1) and got these results.[code="plain"]========== Mark =========================================== SQL Server Execution Times: CPU time = 2090 ms, elapsed time = 588 ms.========== Mark w-MAXDOP 1=========================== SQL Server Execution Times: CPU time = 936 ms, elapsed time = 925 ms.========== Dwain =========================================== SQL Server Execution Times: CPU time = 2605 ms, elapsed time = 1182 ms.[/code]I've always found it extremely interesting how, when SQL parallelizes a query the CPU time goes up quite significantly in relation to the non-parallelized version, without a proportional drop in elapsed time.[/quote]Oops, turns out my query doesn't quite work properly, here's a fixed version. Fortunately doesn't affect performance much.[code="sql"]WITH AllSeq AS ( SELECT DocNum,Seq FROM GR UNION SELECT DocNum,Seq FROM GE UNION SELECT DocNum,Seq FROM Ref)SELECT sq.DocNum, gr.GR, gr.Seq AS GR_Seq, ge.GE, ge.Seq AS GE_Seq, rf.Ref, rf.Seq AS Ref_SeqFROM AllSeq sqLEFT OUTER JOIN GR gr ON gr.DocNum = sq.DocNum AND gr.Seq = sq.SeqLEFT OUTER JOIN GE ge ON ge.DocNum = sq.DocNum AND ge.Seq = sq.SeqLEFT OUTER JOIN Ref rf ON rf.DocNum = sq.DocNum AND rf.Seq = sq.Seq;[/code]Wed, 29 Aug 2012 21:12:18 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspxGus,That's an interesting point on the IOs but in the below test harness (5 docs) it still runs pretty fast, although clearly not as fast as Mark's.[code="sql"]Create table #Doc (DocNum nvarchar(15) NOT NULL);Insert into #Doc (DocNum)Values ('1'),('2'),('3'),('4'),('5');------------------------------Create Table #GR (DocNum nvarchar(15) NOT NULL,GR nvarchar(15) NOT NULL,Seq int NOT NULL)--------------------------------Create Table #GE (DocNum nvarchar(15) NOT NULL,GE nvarchar(15) NOT NULL,Seq int NOT NULL)--------------------------------------------------Create Table #Ref (DocNum nvarchar(15) NOT NULL,Ref nvarchar(15) NOT NULL,Seq int NOT NULL);WITH Tally (number) AS ( SELECT TOP 2000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b)Insert into #GR(DocNum, GR, Seq)SELECT b.number,'GR_'+CAST(a.number AS VARCHAR(10)),a.numberFROM Tally aCROSS APPLY (SELECT number FROM Tally WHERE number BETWEEN 1 AND 5) b--FROM master.dbo.spt_values--WHERE number BETWEEN 1 AND 500 AND type='P';WITH Tally (number) AS ( SELECT TOP 3000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b)Insert into #GE(DocNum, GE, Seq)SELECT b.number,'GE_'+CAST(a.number AS VARCHAR(10)),a.numberFROM Tally aCROSS APPLY (SELECT number FROM Tally WHERE number BETWEEN 1 AND 5) b--FROM master.dbo.spt_values--WHERE number BETWEEN 1 AND 750 AND type='P';WITH Tally (number) AS ( SELECT TOP 4000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b)Insert into #Ref (DocNum, Ref, Seq)SELECT b.number,'Ref_'+CAST(a.number AS VARCHAR(10)),a.numberFROM Tally aCROSS APPLY (SELECT number FROM Tally WHERE number BETWEEN 1 AND 5) b--FROM master.dbo.spt_values--WHERE number BETWEEN 1 AND 1000 AND type='P';GOPRINT '========== Mark ==========================================='DECLARE @DocNum nvarchar(15)DECLARE @GR nvarchar(15)DECLARE @GE nvarchar(15)DECLARE @Ref nvarchar(15)DECLARE @Seq1 intDECLARE @Seq2 intDECLARE @Seq3 intSET STATISTICS TIME ON;WITH AllSeq AS ( SELECT Seq FROM #GR UNION SELECT Seq FROM #GE UNION SELECT Seq FROM #Ref)SELECT @DocNum = d.DocNum, @GR = gr.GR, @Seq1 = gr.Seq, @GE = ge.GE, @Seq2 = ge.Seq, @Ref = rf.Ref, @Seq3 = rf.SeqFROM #Doc dCROSS JOIN AllSeq sqLEFT OUTER JOIN #GR gr ON gr.DocNum = d.DocNum AND gr.Seq = sq.SeqLEFT OUTER JOIN #GE ge ON ge.DocNum = d.DocNum AND ge.Seq = sq.SeqLEFT OUTER JOIN #Ref rf ON rf.DocNum = d.DocNum AND rf.Seq = sq.Seq;SET STATISTICS TIME OFFGOPRINT '========== GSquared ==========================================='DECLARE @DocNum nvarchar(15)DECLARE @GR nvarchar(15)DECLARE @GE nvarchar(15)DECLARE @Ref nvarchar(15)DECLARE @Seq1 intDECLARE @Seq2 intDECLARE @Seq3 intSET STATISTICS TIME ONSELECT @DocNum = COALESCE(#GR.DocNum, #GE.DocNum, #Ref.DocNum), @GR = GR, @Seq1 = #GR.Seq, @GE = GE, @Seq2 = #GE.Seq, @Ref = Ref, @Seq3 = #Ref.SeqFROM #GR FULL OUTER JOIN #GE ON #GR.DocNum = #GE.DocNum AND #GR.Seq = #GE.Seq FULL OUTER JOIN #Ref ON #GE.DocNum = #Ref.DocNum AND #GE.Seq = #Ref.Seq OR #GR.DocNum = #Ref.DocNum AND #GR.Seq = #Ref.Seq;SET STATISTICS TIME OFFGOPRINT '========== Dwain ==========================================='DECLARE @DocNum nvarchar(15)DECLARE @GR nvarchar(15)DECLARE @GE nvarchar(15)DECLARE @Ref nvarchar(15)DECLARE @Seq1 intDECLARE @Seq2 intDECLARE @Seq3 intSET STATISTICS TIME ON;WITH CTE AS ( SELECT n=1, DocNum, Ref, Seq FROM #Ref UNION ALL SELECT 2, DocNum, GE, Seq FROM #GE UNION ALL SELECT 3, DocNum, GR, Seq FROM #GR)SELECT @DocNum=a.DocNum ,@GR=MAX(CASE n WHEN 3 THEN Ref END) ,@Seq1=MAX(CASE n WHEN 3 THEN Seq END) ,@GE=MAX(CASE n WHEN 2 THEN Ref END) ,@Seq2=MAX(CASE n WHEN 2 THEN Seq END) ,@Ref=MAX(CASE n WHEN 1 THEN Ref END) ,@Seq3=MAX(CASE n WHEN 1 THEN Seq END )FROM CTE aINNER JOIN #Doc b ON a.DocNum = b.DocNumGROUP BY a.DocNum, RefSET STATISTICS TIME OFFDROP TABLE #Doc, #Ref, #GR, #GE[/code]Results:[code="other"](10000 row(s) affected)(15000 row(s) affected)(20000 row(s) affected)========== Mark =========================================== SQL Server Execution Times: CPU time = 93 ms, elapsed time = 112 ms.========== GSquared =========================================== SQL Server Execution Times: CPU time = 177918 ms, elapsed time = 180257 ms.========== Dwain =========================================== SQL Server Execution Times: CPU time = 156 ms, elapsed time = 313 ms.[/code]Let me know if I did something wrong (always a possibility before I've had my morning coffee :-)).Interestingly, when I bumped the doc count to 40, Mark's SQL seemed to begin to benefit from parallizing the query (note elapsed ms < CPU ms). So I retried with OPTION (MAXDOP 1) and got these results.[code="plain"]========== Mark =========================================== SQL Server Execution Times: CPU time = 2090 ms, elapsed time = 588 ms.========== Mark w-MAXDOP 1=========================== SQL Server Execution Times: CPU time = 936 ms, elapsed time = 925 ms.========== Dwain =========================================== SQL Server Execution Times: CPU time = 2605 ms, elapsed time = 1182 ms.[/code]I've always found it extremely interesting how, when SQL parallelizes a query the CPU time goes up quite significantly in relation to the non-parallelized version, without a proportional drop in elapsed time.Wed, 29 Aug 2012 19:10:00 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspxHere's a way to generate some simple timing tests.[code="sql"]TRUNCATE TABLE GR;TRUNCATE TABLE GE;TRUNCATE TABLE Ref;Insert into GR(DocNum, GR, Seq)SELECT 1,'GR_'+CAST(number AS VARCHAR(10)),numberFROM master.dbo.spt_valuesWHERE number BETWEEN 1 AND 500 AND type='P'Insert into GE(DocNum, GE, Seq)SELECT 1,'GE_'+CAST(number AS VARCHAR(10)),numberFROM master.dbo.spt_valuesWHERE number BETWEEN 1 AND 750 AND type='P'Insert into Ref (DocNum, Ref, Seq)SELECT 1,'Ref_'+CAST(number AS VARCHAR(10)),numberFROM master.dbo.spt_valuesWHERE number BETWEEN 1 AND 1000 AND type='P';GOPRINT '========== Mark ==========================================='DECLARE @DocNum nvarchar(15)DECLARE @GR nvarchar(15)DECLARE @GE nvarchar(15)DECLARE @Ref nvarchar(15)DECLARE @Seq1 intDECLARE @Seq2 intDECLARE @Seq3 intSET STATISTICS TIME ON;WITH AllSeq AS ( SELECT Seq FROM GR UNION SELECT Seq FROM GE UNION SELECT Seq FROM Ref)SELECT @DocNum = d.DocNum, @GR = gr.GR, @Seq1 = gr.Seq, @GE = ge.GE, @Seq2 = ge.Seq, @Ref = rf.Ref, @Seq3 = rf.SeqFROM Doc dCROSS JOIN AllSeq sqLEFT OUTER JOIN GR gr ON gr.DocNum = d.DocNum AND gr.Seq = sq.SeqLEFT OUTER JOIN GE ge ON ge.DocNum = d.DocNum AND ge.Seq = sq.SeqLEFT OUTER JOIN Ref rf ON rf.DocNum = d.DocNum AND rf.Seq = sq.Seq;SET STATISTICS TIME OFFGOPRINT '========== GSquared ==========================================='DECLARE @DocNum nvarchar(15)DECLARE @GR nvarchar(15)DECLARE @GE nvarchar(15)DECLARE @Ref nvarchar(15)DECLARE @Seq1 intDECLARE @Seq2 intDECLARE @Seq3 intSET STATISTICS TIME ONSELECT @DocNum = COALESCE(dbo.GR.DocNum, dbo.GE.DocNum, dbo.Ref.DocNum), @GR = GR, @Seq1 = dbo.GR.Seq, @GE = GE, @Seq2 = dbo.GE.Seq, @Ref = Ref, @Seq3 = dbo.Ref.SeqFROM dbo.GR FULL OUTER JOIN dbo.GE ON dbo.GR.DocNum = dbo.GE.DocNum AND dbo.GR.Seq = dbo.GE.Seq FULL OUTER JOIN dbo.Ref ON dbo.GE.DocNum = dbo.Ref.DocNum AND dbo.GE.Seq = dbo.Ref.Seq OR dbo.GR.DocNum = dbo.Ref.DocNum AND dbo.GR.Seq = dbo.Ref.Seq;SET STATISTICS TIME OFF[/code]Wed, 29 Aug 2012 08:01:58 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspxAmen, GS.... I have to scale this up to about 5-6 tables with an est. million + rows each.... The results are going to be pumped to a website and I don't think a paying customer wants to wait that long.... Thanks for the try Dwain... everyone is being really cool about this...Crusty.Wed, 29 Aug 2012 07:42:18 GMTCptCrusty1http://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspx[quote][b]dwain.c (8/28/2012)[/b][hr]LEFT JOINs, CROSS JOINs, FULL OUTER JOINs? What's up with that?Why not something a bit simpler like this?[code="sql"];WITH CTE AS ( SELECT n=1, DocNum, Ref, Seq FROM Ref UNION ALL SELECT 2, DocNum, GE, Seq FROM GE UNION ALL SELECT 3, DocNum, GR, Seq FROM GR)SELECT a.DocNum ,GR=MAX(CASE n WHEN 3 THEN Ref END) ,GR_Seq=MAX(CASE n WHEN 3 THEN Seq END) ,GE=MAX(CASE n WHEN 2 THEN Ref END) ,GE_Seq=MAX(CASE n WHEN 2 THEN Seq END) ,Ref=MAX(CASE n WHEN 1 THEN Ref END) ,Ref_Seq=MAX(CASE n WHEN 1 THEN Seq END )FROM CTE aINNER JOIN Doc b ON a.DocNum = b.DocNumGROUP BY a.DocNum, Ref[/code][/quote]Because that solution will cause an I/O blow-up directly proportional to the number of entries in the Doc table.I added docs 2-5, each with 1 entry in the GE table, to the provided sample data.With 1 entry, the I/O on your solution looks like this:[quote]Table 'Doc'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'GR'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'GE'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Ref'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.[/quote]Very nice.With 5 entries, it looks like this:[quote]Table 'GR'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'GE'. Scan count 5, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Ref'. Scan count 5, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Doc'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.[/quote]See how the scans increase directly as you increase the rows in Doc?With 1 entry or with 5 entries, the Full Outer Join solution looks like this:[quote]Table 'Worktable'. Scan count 2, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Ref'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'GR'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'GE'. Scan count 2, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.[/quote]2 scans per table, regardless of number of rows. Logical reads can get out of hand, but both do that, and scan count is usually a bigger deal in terms of overall performance.Try it with a few thousand or a million rows. The I/O stats will be a serious bottleneck on yours, and won't be a big deal at all on the Full Outer version.Wed, 29 Aug 2012 07:38:04 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspxLEFT JOINs, CROSS JOINs, FULL OUTER JOINs? What's up with that?Why not something a bit simpler like this?[code="sql"];WITH CTE AS ( SELECT n=1, DocNum, Ref, Seq FROM Ref UNION ALL SELECT 2, DocNum, GE, Seq FROM GE UNION ALL SELECT 3, DocNum, GR, Seq FROM GR)SELECT a.DocNum ,GR=MAX(CASE n WHEN 3 THEN Ref END) ,GR_Seq=MAX(CASE n WHEN 3 THEN Seq END) ,GE=MAX(CASE n WHEN 2 THEN Ref END) ,GE_Seq=MAX(CASE n WHEN 2 THEN Seq END) ,Ref=MAX(CASE n WHEN 1 THEN Ref END) ,Ref_Seq=MAX(CASE n WHEN 1 THEN Seq END )FROM CTE aINNER JOIN Doc b ON a.DocNum = b.DocNumGROUP BY a.DocNum, Ref[/code]Tue, 28 Aug 2012 19:00:51 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspxGS, I'm going to move this out of the Forum... I could stand to chat with you... LOL... I'll PM you.Tue, 28 Aug 2012 14:17:20 GMTCptCrusty1http://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspx[quote][b]CptCrusty1 (8/28/2012)[/b][hr]@Laurie, Mark, GSquared. I am humbled.I was starting to lean towards Cross Join; however, I'd never had an opportunity to use it before. Seems like it behaves a bit like a pivot table??GSQuared, dito, never used Coalesce before. Guess I need to hit the books and learn these two techniques. The results are exactly what I needed.I will try all three results for performance against a test version with about 100k records....Thanks all.. .I really appreciate your help.Sincerely.Crusty[/quote]Cross Join just joins every row in one table (or dataset) to every row in another table (or dataset). Produces what's called a "Cartesian Product".Coalesce is just IsNull's big brother. All it does is pick the first non-null value in a list. If they're all null, it returns null, otherwise, it gets the first one. Since, in an outer join (Full, Left, or Right), one or more of the columns may be null, I used that to make sure it would get something in that column.The key to my version is Full Outer Join. What that does is get all rows from both sides of the join, whether they have a matching row in the other side or not.So, a Full Outer from GR to GE gets all rows in GR, even if GE doesn't have a matching row. And it gets all rows from GE, even if GR doesn't have a matching row. So if GR had 5 and GE 2, it would still get all rows in both. Then it does the same thing with Ref, getting all rows, even if they don't have a match in the other two. If anything does match, it puts them in the same row, but if it doesn't match, it still pulls it.This kind of problem is exactly what Full Outer Join is designed to handle. It's one of those features of SQL that seems to be less well-known. But, issues with how Joins work is one of the things that gives new database devs major headaches, so it's not too surprising.Tue, 28 Aug 2012 13:48:54 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspx[quote][b]laurie-789651 (8/28/2012)[/b][hr][quote][b]GSquared (8/28/2012)[/b][hr][quote][b]CptCrusty1 (8/28/2012)[/b][hr]Laurie,Thanks for your reply. The "Values" reserved word didn't fly in 2005. Is that a 2008+ term?ThanksCrusty[/quote]That's what's called a "Table Values Function", and it's 2008+. It will also only work with data that's limited to no more than 9 values per set.[/quote]Just for the record, this example is limited to 9, but you can code the numbers table up to any number.[/quote]Yes, but it will still have a finite limit, and is an unnecessary limit on the query. This kind of situation is exactly what Full Outer Join is meant for, so working around it with hard-coded things like that is unnecessary.The numbers table version you came up with isn't a bad solution, it's just unnecessarily complex for the desired end result. That's all. No big deal. And a hard-coded numbers table with a few thousand rows would be backwards compatible, and work for any conceivable data complexity needed by this code. So it's limits aren't that big a deal. Just an Occam fan here.Tue, 28 Aug 2012 13:47:07 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspxI'm going to remember you guys (and gals) and ChristmaHoniQuanzika..... :crying::-DTue, 28 Aug 2012 13:28:41 GMTCptCrusty1http://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspx[quote][b]GSquared (8/28/2012)[/b][hr][quote][b]CptCrusty1 (8/28/2012)[/b][hr]Laurie,Thanks for your reply. The "Values" reserved word didn't fly in 2005. Is that a 2008+ term?ThanksCrusty[/quote]That's what's called a "Table Values Function", and it's 2008+. It will also only work with data that's limited to no more than 9 values per set.[/quote]Just for the record, this example is limited to 9, but you can code the numbers table up to any number.Tue, 28 Aug 2012 13:23:47 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspx[quote][b]CptCrusty1 (8/28/2012)[/b][hr]Laurie,Thanks for your reply. The "Values" reserved word didn't fly in 2005. Is that a 2008+ term?ThanksCrusty[/quote]Hi - Sorry about that, but you've posted in the SQL 2008 area - one to watch out for next time!Yes - it is 2008+Tue, 28 Aug 2012 13:20:14 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspxGS, that's what I thought. I've seen that in a 2008 shop; however, I'm currently in a 2005 shop that is in mid-migration, thus my afore-mentioned quandry.Again... My Thanks.Tue, 28 Aug 2012 13:19:14 GMTCptCrusty1http://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspx@Laurie, Mark, GSquared. I am humbled.I was starting to lean towards Cross Join; however, I'd never had an opportunity to use it before. Seems like it behaves a bit like a pivot table??GSQuared, dito, never used Coalesce before. Guess I need to hit the books and learn these two techniques. The results are exactly what I needed.I will try all three results for performance against a test version with about 100k records....Thanks all.. .I really appreciate your help.Sincerely.CrustyTue, 28 Aug 2012 13:17:50 GMTCptCrusty1http://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspx[quote][b]CptCrusty1 (8/28/2012)[/b][hr]Laurie,Thanks for your reply. The "Values" reserved word didn't fly in 2005. Is that a 2008+ term?ThanksCrusty[/quote]That's what's called a "Table Values Function", and it's 2008+. It will also only work with data that's limited to no more than 9 values per set.The Full Outer Join version I posted will work in any version of SQL Server, at least from 7.5-on (I haven't played with anything prior to that, so can't be sure it will work there), and will work with any number of sub-values.Tue, 28 Aug 2012 13:16:35 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspxLaurie,Thanks for your reply. The "Values" reserved word didn't fly in 2005. Is that a 2008+ term?ThanksCrustyTue, 28 Aug 2012 13:13:45 GMTCptCrusty1http://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspxWhat you need to do is Full Outer Joins instead of Left Outer Joins. Join on Doc ID and Row Number.[code="sql"]SELECT COALESCE(dbo.GR.DocNum, dbo.GE.DocNum, dbo.Ref.DocNum) AS DocNum, GR, dbo.GR.Seq AS GR_Seq, GE, dbo.GE.Seq AS GE_Seq, Ref, dbo.Ref.Seq AS Ref_SeqFROM dbo.GR FULL OUTER JOIN dbo.GE ON dbo.GR.DocNum = dbo.GE.DocNum AND dbo.GR.Seq = dbo.GE.Seq FULL OUTER JOIN dbo.Ref ON dbo.GE.DocNum = dbo.Ref.DocNum AND dbo.GE.Seq = dbo.Ref.Seq OR dbo.GR.DocNum = dbo.Ref.DocNum AND dbo.GR.Seq = dbo.Ref.Seq;[/code]Tue, 28 Aug 2012 10:09:50 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspx[code="sql"]select Doc.DocNum, GR.GR, GR_Seq = GR.Seq, GE.GE, GE_Seq = GE.Seq, Ref.Ref, Ref_Seq = Ref.Seqfrom Doc cross join ( values (1),(2),(3),(4),(5),(6),(7),(8),(9) ) N (n) left outer join GR on GR.DocNum = Doc.DocNum and N.n = GR.Seq left outer join GE on GE.DocNum = DOC.DocNum and N.n = GE.Seq left outer join Ref on Ref.DocNum = DOC.DocNum and N.n = Ref.Seqwhere Ref.Ref is not nullorder by Doc.DocNum, N.n[/code]Tue, 28 Aug 2012 10:05:16 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspx[code="sql"]WITH AllSeq AS ( SELECT Seq FROM GR UNION SELECT Seq FROM GE UNION SELECT Seq FROM Ref)SELECT d.DocNum, gr.GR, gr.Seq AS GR_Seq, ge.GE, ge.Seq AS GE_Seq, rf.Ref, rf.Seq AS Ref_SeqFROM Doc dCROSS JOIN AllSeq sqLEFT OUTER JOIN GR gr ON gr.DocNum = d.DocNum AND gr.Seq = sq.SeqLEFT OUTER JOIN GE ge ON ge.DocNum = d.DocNum AND ge.Seq = sq.SeqLEFT OUTER JOIN Ref rf ON rf.DocNum = d.DocNum AND rf.Seq = sq.SeqORDER BY sq.Seq;[/code]Tue, 28 Aug 2012 10:04:40 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspxhiC rusty hop u will get it by reading below code [li]Create table Doc (DocNum nvarchar(15) NOT NULL);Insert into Doc (DocNum)Values ('1');------------------------------Create Table GR (DocNum nvarchar(15) NOT NULL,GR nvarchar(15) NOT NULL,Seq int NOT NULL)Insert into GR (DocNum, GR, Seq) Values (1,'A',1)Insert into GR (DocNum, GR, Seq) Values (1,'B',2)--------------------------------Create Table GE (DocNum nvarchar(15) NOT NULL,GE nvarchar(15) NOT NULL,Seq int NOT NULL)Insert into GE (DocNum, GE, Seq) Values (1,'A',1)Insert into GE (DocNum, GE, Seq) Values (1,'B',2)Insert into GE (DocNum, GE, Seq) Values (1,'C',3)--------------------------------------------------Create Table Ref (DocNum nvarchar(15) NOT NULL,Ref nvarchar(15) NOT NULL,Seq int NOT NULL)Insert into Ref (DocNum, Ref, Seq) Values (1,'A',1)Insert into Ref (DocNum, Ref, Seq) Values (1,'B',2)Insert into Ref (DocNum, Ref, Seq) Values (1,'C',3)Insert into Ref (DocNum, Ref, Seq) Values (1,'D',4)Insert into Ref (DocNum, Ref, Seq) Values (1,'E',5)-------[b]select * from Refleft join GEon ref.DocNum=GE.DocNum [/b] [/li]See the output of bold part of query as Ref have 5 values "1" in DocNumand GE have 3 values "1" in DocNumso output will have 15 rows as join behave row by row.Tue, 28 Aug 2012 09:49:59 GMTsandeep rawathttp://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspx[quote][b]dwain.c (8/27/2012)[/b][hr]However you have not provided DDL for your tables, nor INSERTs (setups) for your sample data. Remember we're all volunteers here. :-)[/quote]Ok Volunteers... here's your script as promised... I know, I'm slow...[code]Create table Doc (DocNum nvarchar(15) NOT NULL);Insert into Doc (DocNum)Values ('1');------------------------------Create Table GR (DocNum nvarchar(15) NOT NULL,GR nvarchar(15) NOT NULL,Seq int NOT NULL)Insert into GR (DocNum, GR, Seq) Values (1,'A',1)Insert into GR (DocNum, GR, Seq) Values (1,'B',2)--------------------------------Create Table GE (DocNum nvarchar(15) NOT NULL,GE nvarchar(15) NOT NULL,Seq int NOT NULL)Insert into GE (DocNum, GE, Seq) Values (1,'A',1)Insert into GE (DocNum, GE, Seq) Values (1,'B',2)Insert into GE (DocNum, GE, Seq) Values (1,'C',3)--------------------------------------------------Create Table Ref (DocNum nvarchar(15) NOT NULL,Ref nvarchar(15) NOT NULL,Seq int NOT NULL)Insert into Ref (DocNum, Ref, Seq) Values (1,'A',1)Insert into Ref (DocNum, Ref, Seq) Values (1,'B',2)Insert into Ref (DocNum, Ref, Seq) Values (1,'C',3)Insert into Ref (DocNum, Ref, Seq) Values (1,'D',4)Insert into Ref (DocNum, Ref, Seq) Values (1,'E',5)select * from Docselect * from GRSelect * from GEselect * from Ref[/code]OH GREAT WIZARDS OF UBER-CODE! HEAR MY SCHREAKING AND BID ME GOOD TIDINGS!!!Tue, 28 Aug 2012 09:24:16 GMTCptCrusty1http://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspx@ Sandeep,I'm sorry, but I'm not quite sure what it is you're telling me; however, in regards to the data, it's correct. Let me get into some detail.This is the end result of an ETL process using some pretty mixed up text files. Lets say I have one record in a file that is Pipe-Delimited with double quotes indicated text.Using the examples in previous post, when I receive the information I have a text file with the following columns:DocNumGRGEREFThe raw data for the examples provided in the original post:1|"A,B"|"A,B,C"|"A,B,C,D,E,F"The providor of the data has essentially combined multiple rows of data into 1 row. For the GR (Second column), "A,B" is actually 2 rows of data broken by the comman. It should actually look like:[code]Docnum GR1 A1 B[/code]The Sequence number is an attempt at recombining the data and is not apart of the original data. The process in place now uses multiple steps to achieve individual tables for the broken out data exactly as it's shown in the previous examples. The problem is that they combine it all back together with Cartesian joins creating a massive landing table.I've consolidated the cleaning of the data into a single procedure; however, I'm trying to recombine the data so that it's not a cartesian join. All the information in the single record pertains to DocNum 1; however, the end result doesn't necessarily have to be totally organized ABC, etc. Every value needs to be in a record with DocNum 1. I know this is a very confusing process. The final destination for the data is totally wierd, and will make a dba have a heart attack; however, there is a method to the madness and it works. I'm trying to streamline the process of getting their data to the final step. Also, the example I provided is significantly simplified... but it's the root of the problem.I'm trying to make it clearer.... .. sorry.Crusty. :ermm:Tue, 28 Aug 2012 07:57:10 GMTCptCrusty1http://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspx[quote][b]CptCrusty1 (8/27/2012)[/b][hr]Ok, so I think I should know the answer to this but I'm getting nothing but what looks like a cartesian product... yuk. I have 1 table that has a list of master document numbers... for the sake of argument, there is 1 doc num, 1 record.There are 3 tables that have values that relate to the DocNum in the Doc Tbl; however, they all don't totally relate to each other. Confused yet?? :-DThe DocNums are related. However, the Seqence numbers in the other tables determine how they should appear in the final results table.[code]Doc Tbl GR Tbl GE tbl Ref tbl DocNums Docnum GR Seq DocNum GE Seq DocNum Ref Seq1 1 A 1 1 A 1 1 A 1 1 B 2 1 B 2 1 B 2 1 C 3 1 C 3 1 D 4 1 E 5 [/code] If I ignore the master list of Doc Numbers in the Doc tbl and Left Join GR to GE on DocNum and Seq, I get:[code]Docnum GR GR_Seq GE GE_Seq1 A 1 A 11 B 2 B 21 NULL NULL C 3[/code]It starts getting whonky if I add the third table because the Sequence number relates to the other two tables. There is no gaurantee that the quantity of sequence numbers is going to be the same in each table. GE could have 5 in the sequence, while GR has 1, and REF has 15. What's [i]REALY[/i] chunking up the whole thing is if I try to refer it back to the master list of DocNums. When I try to do that the whole thing blows up and I get what looks to be a cartesian product... My end result should look like this:[code]Docnum GR GR_Seq GE GE_Seq Ref Ref_Seq1 A 1 A 1 A 11 B 2 B 2 B 21 NULL NULL C 3 C 31 NULL NULL NULL NULL D 41 NULL NULL NULL NULL E 5[/code][/quote]Hi you are joining "DocNums" as in first part (query) there is no repeat of the value of "DocNum" 1 in second query there is repeat of doc no .if there is repeat of joining data returns multiple record.As it works row by row...Mon, 27 Aug 2012 21:42:15 GMTsandeep rawathttp://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspxI will provide some DDL for the tables in the morning... Thanks for the positive reply... :)Mon, 27 Aug 2012 20:36:47 GMTCptCrusty1http://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspxYou've given us expected results, which is a good thing. Some people like me visualize solutions most easily when this is provided.However you have not provided DDL for your tables, nor INSERTs (setups) for your sample data. And the format that you posted them in would be a pain to convert.I think if you do this, someone is going to be able to make short work of your question.Remember we're all volunteers here. :-)Mon, 27 Aug 2012 18:37:10 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1350594-391-1.aspxOk, so I think I should know the answer to this but I'm getting nothing but what looks like a cartesian product... yuk. I have 1 table that has a list of master document numbers... for the sake of argument, there is 1 doc num, 1 record.There are 3 tables that have values that relate to the DocNum in the Doc Tbl; however, they all don't totally relate to each other. Confused yet?? :-DThe DocNums are related. However, the Seqence numbers in the other tables determine how they should appear in the final results table.[code]Doc Tbl GR Tbl GE tbl Ref tbl DocNums Docnum GR Seq DocNum GE Seq DocNum Ref Seq1 1 A 1 1 A 1 1 A 1 1 B 2 1 B 2 1 B 2 1 C 3 1 C 3 1 D 4 1 E 5 [/code] If I ignore the master list of Doc Numbers in the Doc tbl and Left Join GR to GE on DocNum and Seq, I get:[code]Docnum GR GR_Seq GE GE_Seq1 A 1 A 11 B 2 B 21 NULL NULL C 3[/code]It starts getting whonky if I add the third table because the Sequence number relates to the other two tables. There is no gaurantee that the quantity of sequence numbers is going to be the same in each table. GE could have 5 in the sequence, while GR has 1, and REF has 15. What's [i]REALY[/i] chunking up the whole thing is if I try to refer it back to the master list of DocNums. When I try to do that the whole thing blows up and I get what looks to be a cartesian product... My end result should look like this:[code]Docnum GR GR_Seq GE GE_Seq Ref Ref_Seq1 A 1 A 1 A 11 B 2 B 2 B 21 NULL NULL C 3 C 31 NULL NULL NULL NULL D 41 NULL NULL NULL NULL E 5[/code]My recordsets could have 1000-1000000 records on any given day... I feel like I should be able to do this; however, I feel like I've overlooked something basic.Any ideas anyone? Bueller?ThanksCrusty.Mon, 27 Aug 2012 14:45:32 GMTCptCrusty1