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++
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.