I'm using a SQL Server stored procedure to generate an Excel report. Basically each "entry" in the report has three parts: Object, Observation, and Points. The observations have a reference to the primary key of an object, and the points have a reference to an observation. That is the relationship between the parts. (Sorry if my terminology is off, I am used to writing in C# or Java but still learning SQL.)
I want the output to be like this:
-Object
-All Observations linked to that Object
- Under each observation there should be all the points linked to that observation
And repeat for each object. I have this working using a cursor for generating all observations with each object, but I can't get the points to come up under each observation.
I have read all over the Internet that cursors are Bad, and I assume this means nested cursors are Very Bad. But this is what I have so far. If you know of a better way to do this, please tell me. I am open to totally refactoring this if necessary.
DECLARE @Object_Cursor cursor
DECLARE @Observation_Cursor cursor
DECLARE @i int
DECLARE @j int
DECLARE @row int = 1
SET @Object_Cursor = CURSOR FOR (SELECT PK_ObjectId FROM Objects)
OPEN @Object_Cursor
FETCH NEXT FROM @Object_Cursor INTO @i
While @@FETCH_STATUS = 0
Begin
Select
PK_ObjectID,
Name,
Timestamp
From Objects where PK_ObjectID = @i
SET @Observation_Cursor = CURSOR FOR (SELECT @PK_ObservationID FROM Observations)
OPEN @Observation_Cursor
FETCH NEXT FROM @Observation_Cursor INTO @j
While @@FETCH_STATUS = 0
Begin
Select
Timestamp,
Note,
User
from Observations
where FK_ObjectId = @i
order by FK_ObjectID
Select Lat, Lng
From Pts
Where FK_ObservationID = @j
Fetch Next From @Observation_Cursor into @j
END
FETCH NEXT FROM @Object_Cursor into @i
END
Close @Observation_Cursor
DEALLOCATE @Observation_Cursor
CLOSE @Object_Cursor
DEALLOCATE @Object_Cursor
I feel like the order I'm executing these loops is off somehow, but I haven't been able to get it. Current output is:
-Object
-Observations for that object
-empty points result set
These results are repeated several times for the same object before it goes to the next one, and the points are always empty.
Any help or pointers in another direction is much appreciated.
C# loop logic isn't how you want to think for SQL...1 statement is always preferable.
I think this is close to what you want...i'm guessing a bit at your keys in the join.
Select
(pick your fields here)
From Objects
left join Observations on objects.PK_ObjectID = Observations.FK_ObjectID
left join Pts on Pts.FK_ObservationID = observations.pk_observation_ID