|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
November 4th, 2004, 08:18 AM
|
|||
|
|||
|
Inner joins and aliases
Hello,
I am converting a sql server 2000 sql statement, that I know works, to an oracle compliant version. However, I am running into some great difficulty getting it to work. Pretend there are 2 tables, Table: Student Columns: ID, Name Table: Course Columns: ID, CourseName, StudentId, CourseType (StudentId is a foreign key to the student table) I want to get a count of all math courses that a student is taking. I also want to get the course names. I MUST use an outer join (the reason why is because these tables are simplistic versions of my real tables) For my sql server statement, I have: SELECT s.[ID], s.[Name], c.CourseName, c.CourseCount FROM Student s LEFT OUTER JOIN (SELECT count(*) as 'CourseCount', CourseName, StudentId FROM Course WHERE CourseType='Math') c ON s.[ID] = c.StudentId Thank you for your help. It is very appreciated!
|
|
#2
November 4th, 2004, 11:42 AM
|
|||
|
|||
|
From your description it seems as you want
Code:
SELECT s.ID,
s.Name,
c.CourseName,
(select count(*)
from course
where courceType = 'Math'
and studentid = c.studentid as ) CourseCount
FROM Student s
LEFT OUTER JOIN Course c ON s.ID = c.StudentId
and c.courseType = 'Math'
If not, supply further details such as table layout, sample data and expected result. The query you posted will not work in any DBMS since you are mixing aggregate functions and columns without a group by in the derived table.
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > Inner joins and aliases |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
