Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
teraquendya
Starting Member
1 Post |
Posted - 2013-09-25 : 17:01:45
|
Hey, I am trying to convert what is now a c loop generating thousands of sql queries into just one, or perhaps a few.We have four tables:TABLE1: Id, fieldxTABLE2: Id, field1TABLE3: Id, field2, fieldy, fieldz, creationdateTABLE4: Id, field3, fieldwI have gone ahead and merged the sql generated by the code with the code. Hope it looks fairly readable.sum = 0foreach( t1 in 'select * from TABLE1 where fieldx = someinput'){ foreach (t2 in 'select * from TABLE2 where field1 = t1.Id') { var t3 = 'select top 1 * from TABLE3 where field2 = t2.Id and fieldy = true order by creationdate desc' if (t3 != null && t3.fieldz == null) { var t4 = 'select top 1 * from TABLE4 where field3 = t3.Id && fieldw = false' if( t4 == null) sum++ } }}return sum Now I know how to merge the first two lines with a join, but after that I am starting to get a bit lost. I have a feeling that I will need at least one, maybe multiple subqueries... All I need is the count at the end.Any pointers would be appreciated. |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-09-25 : 18:04:35
|
[code]SELECT COUNT(*)FROM dbo.Table1 AS t1INNER JOIN dbo.Table2 AS t2 ON t2.Field1 = t1.IDCROSS APPLY ( SELECT TOP(1) t3.ID FROM dbo.Table3 AS t3 WHERE t3.Field2 = t2.ID AND t3.FieldY = 1 ORDER BY t3.CreationDate DESC ) AS t3(ID)OUTER APPLY ( SELECT TOP(1) t4.Field3 FROM dbo.Table4 AS t4 WHERE t4.Field3 = t3.ID AND t4.FieldW = 0 ) AS t4(Field3)WHERE t1.FieldX = @SomeInput AND t3.FieldZ IS NULL AND t4.Field3 IS NULL;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-09-28 : 04:15:07
|
[code]SELECT COUNT(*)FROM TABLE1 t1INNER JOIN TABLE2 t2ON t2.field1 = t1.IdINNER JOIN (SELECT FieldZ,ROW_NUMBER() OVER (PARTITION BY field2 ORDER BY creationdate DESC) AS Rn FROM TABLE3 WHERE fieldy = 1 )t3ON t3.field2 = t2.IdAND t3.Rn = 1 LEFT JOIN (SELECT Field3,ROW_NUMBER() OVER (PARTITION BY field3 ORDER BY field3 DESC) AS Rn FROM TABLE4 WHERE fieldw = 0 )t4ON t4.field3 = t3.IdAND t4.RN = 1WHERE t4.Field3 IS NULL[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|