July 3, 2009 at 10:46 am
I have three tables; Company, Projects and Notes.
Company is made up of: company_id(PK), company_name, company_city
Projects is made up of: project_id(PK), company_id(FK), project_name
Notes is made up of: note_id(PK), project_id(FK), note_text, note_date, note_author
I would like to have returned:
company_id, company_name, company_city, [no of projects], [most recent note date], [author of that most recent note]
Before I complicated it with note_author, I had the following command:
SELECT company.company_id, company.company_name, company.company_city, count(projects.project_id), MAX(notes.note_date) as 'most_recent_note_date'
FROM company
LEFT OUTER JOIN projects ON projects.company_id = company.company_id
LEFT OUTER JOIN notes ON notes.project_id = projects.project_id
GROUP BY company.company_id, company.company_name, company.company_city
This gave me a single row for each company showing the total number of projects, and the date of the most recent note, or null if there were no notes.
How do I modify this query to also bring back the note_author of the most recent note?
When I add notes.note_author into my SELECT and GROUP BY statements, I get row for each note.
What am I doing wrong?
July 3, 2009 at 12:23 pm
Before I continue, please verify that the test environment I have created below is at least some what accurate regarding your problem. Please note a couple of things here.
One, you really should have done al of this for us. Please look carefully at everything in the post so that in the future you can do this for other problems you encounter and require assistance.
Two, I made slight changes based on how I do things, but it should not have changed the basics of your problem. If there is anything that needs to be added, please do.
--Company is made up of: company_id(PK), company_name, company_city
--Projects is made up of: project_id(PK), company_id(FK), project_name
--Notes is made up of: note_id(PK), project_id(FK), note_text, note_date, note_author
--I would like to have returned:
--company_id, company_name, company_city, [no of projects], [most recent note date], [author of that most recent note]
--
--Before I complicated it with note_author, I had the following command:
--SELECT company.company_id, company.company_name, company.company_city, count(projects.project_id), MAX(notes.note_date) as 'most_recent_note_date'
--FROM company
--LEFT OUTER JOIN projects ON projects.company_id = company.company_id
--LEFT OUTER JOIN notes ON notes.project_id = projects.project_id
--GROUP BY company.company_id, company.company_name, company.company_city
create table dbo.Company( -- Create Company Table
CompanyID int primary key,
CompanyName varchar(32),
CompanyCity varchar(32)
);
create table dbo.Project( -- Create Project Table
ProjectID int primary key,
CompanyID int,
ProjectName varchar(32)
);
create table dbo.Note( -- Create Note Table
NoteID int primary key,
ProjectID int,
NoteDate datetime,
NoteAuthor varchar(32),
NoteText varchar(max)
);
insert into dbo.Company -- Insert test data to Company table
select 1,'ABC Corp','Some Town' union all
select 2,'ACME Inc','Another Town';
insert into dbo.Project -- Insert test data to Project table
select 1,1,'Simple Server Install' union all
select 2,1,'SAP Install' union all
select 3,2,'SQL Server Cluster Install' union all
select 4,2,'AXAPTA Install';
insert into dbo.Note -- Insert test data to Note table
select 1,1,getdate() - 3, 'Tom','Missing power cables for server' union all
select 2,1,getdate() - 2, 'Tom','Server cables arrived over-night' union all
select 3,2,getdate() - 4, 'Joe','SAP Install going great' union all
select 4,2,getdate(), 'Joe','New server up, creating reporting database for SAP system' union all
select 5,3,getdate() - 5, 'Sam','Having problems with cluster install' union all
select 6,3,getdate() - 3, 'Sam','Found the problem, disk controller not on HAL' union all
select 7,4,getdate() - 1, 'Tom','AXAPTA install now in progress, Cluster working great. Thanks Sam' union all
select 8,4,getdate(), 'Tom','AXAPTA install complete, users loading configuration data';
select -- Verify data in Company table
*
from
dbo.Company;
select -- Verify data in Project table
*
from
dbo.Project;
select -- Verify data in Note table
*
from
dbo.Note;
SELECT
Company.CompanyID,
Company.CompanyName,
Company.CompanyCity,
count(Project.ProjectID) as ProjectCount,
MAX(Note.NoteDate) as LatestNoteDate
FROM
dbo.Company
LEFT OUTER JOIN dbo.Project
ON Project.CompanyID = Company.CompanyID
LEFT OUTER JOIN dbo.Note
ON Note.ProjectID = Project.ProjectID
GROUP BY
Company.CompanyID,
Company.CompanyName,
Company.CompanyCity;
--
-- Started work on a CTE based solution, but stopped. Meant to cut this part out but missed it
--
-- )
-- select
-- CompanyID,
-- CompanyName,
-- CompanyCity,
-- ProjectCount,
-- LatestNoteDate;
--
drop table dbo.Company; -- drop test table
drop table dbo.Project; -- drop test table
drop table dbo.Note; -- drop test table
July 3, 2009 at 1:37 pm
@Lynn - it looks like the beginning of your CTE is missing, and the select from it isn't referencing the CTE. Otherwise, very nicely done.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 3, 2009 at 3:10 pm
WayneS (7/3/2009)
@Lynn - it looks like the beginning of your CTE is missing, and the select from it isn't referencing the CTE. Otherwise, very nicely done.
Opps, I meant to cut that part out. I started working on the solution, and stopped, deciding I needed to be sure what I had was a good base to start from. I'll edit the code.
July 4, 2009 at 1:28 pm
Lynn,
Thanks for the advice. I didn't think to include the code to create the environment I am using.
I will do so in future.
What was in your code samples looks fine. I appreciate any help I can get.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply