CODEWITHSUNDEEP
sqloracle-databasenested
RhumB
RhumB

Reputation: 141

sql invalid identifier, using alias

select student.ID, course_id
from (
  select student.ID, course_id
  from student,takes 
  where student.id = takes.id
  group by student.ID,course_id
  having count(student.ID) >1
  )
group by student.id
having count(*)>2

I am new to sql, and struggling with the nested subqueries. This is what I did, trying to find who have retaken at least 3 courses at least once. it didn't work. and I know it seems weird too...

Upvotes: 1

Views: 1770

Answers (1)

Robert Rodkey
Robert Rodkey

Reputation: 423

Full disclosure: I haven't tested this for syntax errors, but I did see two main issues with your code. A: You need to give an alias to your subquery B: You need to use the variable names within your subquery along with that new alias in your outer query (note that I've assigned an alias of "student_id" for student.id in your inner query)

select 
    retakes.student_id,
    retakes.course_id
from 
(
    select 
        student.ID student_id,
        course_id
    from student,takes 
    where student.id = takes.id
    group by student.ID, course_id
    having count(*) >1
) retakes
group by retakes.student_id
having count(*)>2

It would be helpful if you included the error that you're getting with your original post (or with any subsequent answers).

Upvotes: 1

Related Questions