Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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.
 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
30421 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
52326 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  
 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.1 seconds. Powered By: Snitz Forums 2000