StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POcomplex sql query - merge or replace groups
text
Body
copied!<p>Im building a school website and got stuck on this problem.</p> <p>The SQL database is structured as follows ( i do not have permission to modify their database)</p> <pre><code>**GroupRecords** Id (int, primary key) Name (nvarchar) SchoolYear (datetime) RecordDate (datetime) IsUpdate (bit) **People** Id (int, primary key) GroupRecordsId (int, foreign key to GroupRecords.Id) Name (nvarchar) Bio (nvarchar) Location (nvarchar) </code></pre> <p>The actual paper form looks like this, it gets updated throughout the year Assume no duplicate Participant names (People.Name) in the same form (GroupRecords)</p> <pre><code>------------------------- Name: District A SchoolYear: 2000 RecordDate: 12/30/1999 IsUpdate: no Participants Name Location AA 11 BB 22 CC 33 DD 44 ------------------------- ------------------------- Name: District A SchoolYear: 2000 RecordDate: 1/2/2000 IsUpdate: no Participants Name Location QQ 33 DD 22 EE 99 FF 66 ------------------------- ------------------------- Name: District A SchoolYear: 2000 RecordDate: 2/1/2000 IsUpdate: yes Participants Name Location XX 00 ------------------------- ------------------------- Name: District A SchoolYear: 2000 RecordDate: 2/1/2000 IsUpdate: yes Participants Name Location QQ 44 ------------------------- </code></pre> <p>now here's the tricky part if IsUpdate is yes, then the list of people returned should merge with the previous record's list (so if IsUpdate is no: replace, if IsUpdate is yes: merge)</p> <p>so if the query is GroupRecords.Name = 'District A' AND GroupRecords.SchoolYear = '1/1/2000', i should get</p> <pre><code>QQ 44 DD 22 EE 99 FF 66 XX 00 </code></pre> <p>It would probably be better to write a stored procedure for this right? thank you so much</p> <pre><code>SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[GroupRecords]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](500) NOT NULL, [SchoolYear] [datetime] NOT NULL, [RecordDate] [datetime] NOT NULL, [IsUpdate] [bit] NOT NULL, CONSTRAINT [PK_GroupRecords] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[People]( [Id] [int] IDENTITY(1,1) NOT NULL, [GroupRecordsId] [int] NOT NULL, [Name] [nvarchar](500) NOT NULL, [Bio] [nvarchar](4000) NOT NULL, [Location] [nvarchar](100) NOT NULL, CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[People] WITH CHECK ADD CONSTRAINT [FK_People_GroupRecords] FOREIGN KEY([GroupRecordsId]) REFERENCES [dbo].[GroupRecords] ([Id]) GO ALTER TABLE [dbo].[People] CHECK CONSTRAINT [FK_People_GroupRecords] </code></pre> <p><strong>Update</strong> let me clearify how IsUpdate works</p> <p>the forms in the example are already sorted by RecordDate in desending order say only the first form exists, the returned list would be</p> <pre><code>AA 11 BB 22 CC 33 DD 44 </code></pre> <p>if only the first and form, the returned list would be</p> <pre><code>QQ 33 DD 22 EE 99 FF 66 </code></pre> <p>since isUpdate in the latest form (the second form) is no, the returned list would just be the content in the 2nd form</p> <p>first 3 forms</p> <pre><code>QQ 33 DD 22 EE 99 FF 66 XX 00 </code></pre> <p>because isUpdate in the latest form (the 3rd form) is yes, its content would add/replace the last list.</p> <p>say there's a fifth form like this</p> <pre><code>------------------------- Name: District A SchoolYear: 2000 RecordDate: 2/10/2000 IsUpdate: no Participants Name Location TT 99 ------------------------- </code></pre> <p>then the returned data would just be</p> <pre><code>TT 99 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload