Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mparter
Yak Posting Veteran
86 Posts |
 Posted - 2007-08-03 : 04:23:15
|
Using the tables below, I need to find out the number of distinct locations for the courses in the selected area.CREATE TABLE [dbo].[CourseAreas] ( [AreaCode] [int] IDENTITY (1, 1) NOT NULL , [Area] [nvarchar] (50)) ON [PRIMARY]CREATE TABLE [dbo].[Courses] ( [CourseCode] [int] IDENTITY (1, 1) NOT NULL , [CourseName] [nvarchar] (50)) ON [PRIMARY]CREATE TABLE [dbo].[CourseInstances] ( [CourseInstanceCode] [int] IDENTITY (1, 1) NOT NULL , [CourseCode] [int] NOT NULL , [AreaCode] [int] NOT NULL , [LocationCode] [int] NOT NULL ) ON [PRIMARY]CREATE TABLE [dbo].[DeliveryLocations] ( [LocationCode] [int] IDENTITY (1, 1) NOT NULL , [Location] [int] NOT NULL) ON [PRIMARY]So if a user selects an area called Engineering (ID 1), then the query should return a list of all the locations that the courses within this area are run at and also a count of the number of courses which are running at these locations.It's probably simple for you guys but I can't get my head around it  Thanks. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-03 : 04:30:57
|
Make use of INNER JOINT and COUNT(DISTINCT ...)
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
|
|
|
Confusing SELECT Query