SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help converting c code to more efficient sql.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

teraquendya
Starting Member

1 Posts

Posted - 09/25/2013 :  17:01:45  Show Profile  Reply with Quote
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.

Edited by - teraquendya on 09/25/2013 17:02:01

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/25/2013 :  18:04:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/28/2013 :  04:15:07  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000