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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help converting c code to more efficient sql.

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, fieldx

TABLE2: Id, field1

TABLE3: Id, field2, fieldy, fieldz, creationdate

TABLE4: Id, field3, fieldw


I have gone ahead and merged the sql generated by the code with the code. Hope it looks fairly readable.


sum = 0
foreach( 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 t1
INNER JOIN dbo.Table2 AS t2 ON t2.Field1 = t1.ID
CROSS 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-28 : 04:15:07
[code]
SELECT COUNT(*)
FROM TABLE1 t1
INNER JOIN TABLE2 t2
ON t2.field1 = t1.Id
INNER JOIN (SELECT FieldZ,ROW_NUMBER() OVER (PARTITION BY field2 ORDER BY creationdate DESC) AS Rn FROM TABLE3 WHERE fieldy = 1 )t3
ON t3.field2 = t2.Id
AND t3.Rn = 1
LEFT JOIN (SELECT Field3,ROW_NUMBER() OVER (PARTITION BY field3 ORDER BY field3 DESC) AS Rn FROM TABLE4 WHERE fieldw = 0 )t4
ON t4.field3 = t3.Id
AND t4.RN = 1
WHERE t4.Field3 IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -