RE: newbie, querie help/advice

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: jose g. de jesus jr mcp, mcdba (josegdejesusjrmcpmcdba_at_discussions.microsoft.com)
Date: 01/20/05 

Date: Wed, 19 Jan 2005 20:53:02 -0800

SELECT Student_Name
FROM dbo.IS_REGISTERED INNER JOIN
                      dbo.STUDENT ON dbo.IS_REGISTERED.Student_ID =
dbo.STUDENT.Student_ID LEFT OUTER JOIN
                      dbo.COURSE RIGHT OUTER JOIN
                      dbo.[SECTION] ON dbo.COURSE.Course_ID =
dbo.[SECTION].Course_ID ON dbo.IS_REGISTERED.Section_ID =
dbo.[SECTION].Section_ID
where course_name = 'database'
and student_name in
(
SELECT dbo.STUDENT.Student_Name
FROM dbo.IS_REGISTERED INNER JOIN
                      dbo.STUDENT ON dbo.IS_REGISTERED.Student_ID =
dbo.STUDENT.Student_ID LEFT OUTER JOIN
                      dbo.COURSE RIGHT OUTER JOIN
                      dbo.[SECTION] ON dbo.COURSE.Course_ID =
dbo.[SECTION].Course_ID ON dbo.IS_REGISTERED.Section_ID =
dbo.[SECTION].Section_ID
where course_name = 'networking'
)

--hope it helps

"Patrick" wrote:

> For part of a school project with MS SQL2000, I have to write a query to
> answer:
>
> Which students are enrolled in both Databases and Networking? (Hint: Use the
> SECTION_ID for each class so you can determine the answer from the
> IS_REGISTERED table by itself.)
>
> Here are the tables:
>
> * is PK, ** is FK, but that hardly even matters with this select. Table
> structures are listed below everything else.
>
> Student (Student_ID*, Student_Name)
>
> Course (Course_ID*, Course_name)
>
> Section (Section_ID**, Course_ID**)
>
> Is_Registered (Student_ID**, Section_ID**)
>
> Here is as close as I get. I can get a correct answer (Student_name is
> taking Course_name) when using only one argument in the where clause, but
> when I use 2 arguments (ie, where this=whatever AND that=something else) I
> should still get one row, but I get zero. My data is verified and
> reverified. My query is wrong, I think. I would like to have a
> recommendation for a book on how to write a select query with 3 or 4 joins,
> too. TIA! here's my attempt:
>
> SELECT STUDENT.Student_Name, COURSE.Course_Name
> FROM COURSE INNER JOIN
> SECTION ON COURSE.Course_ID = SECTION.Course_ID INNER JOIN
> IS_REGISTERED ON [SECTION].Section_ID = IS_REGISTERED.Section_ID INNER
> JOIN
> STUDENT ON IS_REGISTERED.Student_ID = STUDENT.Student_ID
> WHERE
> (COURSE.Course_Name = 'Databases') and (Course.Course_Name = 'Networking)
>
> psully at eatel dot net
>
> PS:
> Here is part of the script I used to write part of the database/tables. Only
> the relevant parts included, I hope.
>
> CREATE TABLE STUDENT
> (
> Student_ID int
> PRIMARY KEY CLUSTERED NOT NULL,
> Student_Name varchar(30)
> )
>
> INSERT STUDENT VALUES (38214, 'Letersky')
> INSERT STUDENT VALUES (54907, 'Alveter')
> INSERT STUDENT VALUES (66324,'Aiken')
> INSERT STUDENT VALUES (70542, 'Marra')
>
> CREATE TABLE IS_REGISTERED
> (A
> Student_ID int NOT NULL,
> Section_ID int NOT NULL,
> Semester varchar(10)
> )
>
> INSERT IS_REGISTERED VALUES (38214, 2714, 'I-2001')
> INSERT IS_REGISTERED VALUES (54907, 2714, 'I-2001')
> INSERT IS_REGISTERED VALUES (54907, 2715, 'I-2001')
> INSERT IS_REGISTERED VALUES (66324, 2713, 'I-2001')
>
>
> CREATE TABLE COURSE
> (
> Course_ID varchar(10)
> PRIMARY KEY CLUSTERED NOT NULL,
> Course_Name varchar(50)
> )
>
> INSERT COURSE VALUES ('ISM 3113', 'Syst Analysis')
> INSERT COURSE VALUES ('ISM 3112', 'Syst Design')
> INSERT COURSE VALUES ('ISM 4212', 'Database')
> INSERT COURSE VALUES ('ISM 4930', 'NetWorking')
>
> CREATE TABLE SECTION
> (
> Section_ID int
> PRIMARY KEY CLUSTERED NOT NULL,
> Course_ID varchar(10)
> )
>
> INSERT SECTION VALUES (2712, 'ISM 3113')
> INSERT SECTION VALUES (2713, 'ISM 3113')
> INSERT SECTION VALUES (2714, 'ISM 4212')
> INSERT SECTION VALUES (2715, 'ISM 4930')
>
> ALTER TABLE [dbo].[IS_REGISTERED] ADD
> CONSTRAINT [FK_IS_REGISTERED_SECTION] FOREIGN KEY
> (
> [Section_ID]
> ) REFERENCES [dbo].[SECTION] (
> [Section_ID]
> ),
> CONSTRAINT [STUDENT_IS_REGISTERED] FOREIGN KEY
> (
> [Student_ID]
> ) REFERENCES [dbo].[STUDENT] (
> [Student_ID]
> )
> GO
>
>
> ALTER TABLE [dbo].[SECTION] ADD
> CONSTRAINT [FK_SECTION_COURSE] FOREIGN KEY
> (
> [Course_ID]
> ) REFERENCES [dbo].[COURSE] (
> [Course_ID]
> )
>
>
> ALTER TABLE IS_REGISTERED ADD
> CONSTRAINT [PK_IS_REGISTERED_STUDENT] PRIMARY KEY(Student_ID, Section_ID)
>
>
>
>

Relevant Pages

  • derived field problem
    ... The database is SQLServer. ... The first field is a difference of 2 dates. ... SELECT CAST(PFCL.DaysStatusChanged as Int) as "DaysStatusChanged", ... dbo.Bank.BankNumber LEFT OUTER JOIN ...
    (microsoft.public.data.ado)
  • RE: How to carry forward a balance or last known value
    ... CREATE TABLE dbo.ShowMeTheMissingDays(col1 int) ... from dbo.ShowMeTheMissingDays x1 LEFT OUTER JOIN dbo.ShowMeTheMissingDays x2 ... > database and if possible in the OLAP report. ...
    (microsoft.public.sqlserver.dts)
  • Re: SQL Server Functions
    ... CREATE FUNCTION FN_IMS_RX_ITEMS (@DateTimeOfExtract DATETIME, ... > @DataSupplyGroup INT) ... > DispensedPIP INT, ... > LEFT OUTER JOIN ...
    (microsoft.public.sqlserver.programming)
  • Re: Tuning Experts
    ... The database is on a remote ... 11 osi left outer join usr asgnusr on ... 197285 bytes received via SQL*Net from client ... Does the database have a 2KB block size? ...
    (comp.databases.oracle.server)
  • Re: urgent ! outer join not picking all records
    ... I did just now change that option for the database I'm working with, ... results are the same - I'm still not getting true outer join results. ... "Van T. Dinh" wrote: ... you are using JET syntax which is different. ...
    (microsoft.public.access.queries)