May 19, 2008 at 8:28 pm
Heh. ROTFLMAO! Oh, the irony!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 22, 2008 at 1:58 pm
Given a table Courses, with columns CourseID ('CIS101', etc) and Description and a table Classes, with columns ClassID (111, 112, etc), CourseID (FK to Courses table) and BeginDate, this will do the trick:
select c.CourseID as Course,
l.ClassID as Room,
l.BeginDate as Date,
case when x1.CourseID is not null
then c.Description
else '' end as Description
from dbo.Courses c
join dbo.Classes l
on c.CourseID = l.CourseID
left join (
select c1.CourseID, Min( l1.ClassID ) as MinID
from dbo.Courses c1
join dbo.Classes l1
on c1.CourseID = l1.CourseID
group by c1.CourseID
) x1
on x1.MinID = l.ClassID;
If you want the DDL:
create table Courses(
CourseID varchar(50) NOT NULL primary key,
Description varchar(500) NOT NULL,
);
create table dbo.Classes(
ClassID int NOT NULL primary key,
CourseID varchar(50) NULL,
BeginDate datetime NOT NULL,
);
ALTER TABLE dbo.Classes WITH NOCHECK
ADD CONSTRAINT FK_Classes_Courses
FOREIGN KEY(CourseID)
REFERENCES dbo.Courses (CourseID);
ALTER TABLE dbo.Classes
CHECK CONSTRAINT FK_Classes_Courses;
insert into dbo.Courses( CourseID, Description)
select 'CIS101', 'Basic CIS class' union all
select 'CIS102', 'Intermediate CIS class' union all
select 'CIS103', 'Advanced CIS class';
insert into dbo.Classes( ClassID, CourseID, BeginDate )
select 111, 'CIS102', '2008-01-01' union all
select 112, 'CIS102', '2008-01-01' union all
select 123, 'CIS101', '2008-01-01' union all
select 124, 'CIS101', '2008-01-01' union all
select 125, 'CIS101', '2008-01-01' union all
select 136, 'CIS103', '2008-01-01';
I'm not making any claims as to efficiency -- you would probably be better off if, as others have advised, to let the front-end do it. But if you are only working with a couple hundred rooms or less, it shouldn't be too bad.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply