CODEWITHSUNDEEP
sqloracle-database
Josh Newlin
Josh Newlin

Reputation: 108

Do aliases in SQL work like this?

In class we have 3 databases, Album (which holds the album number or ano, and mno (the musician who is the producer)), Song (ano (which album it's on), sno (the number associated with this certain song)), and Perform (sno (the song being performed), and mno (the performing artist))

So, simplistically we have

ALBUM (ANO, MNO)

SONG (ANO, SNO)

PERFORM (SNO, MNO);

The task is to find an album's ANO that has a song or songs on it that has been performed by someone other than the producer.

My query is this

SELECT ALBUM.ANO AS ALBUM_NUM
FROM ALBUM
WHERE ALBUM.MNO != (
  SELECT PERFORM.MNO
  FROM PERFORM
  WHERE PERFORM.SNO IN (
    SELECT SONG.SNO
    FROM SONG
    WHERE SONG.ANO = ALBUM_NUM
  )
);

And I get this error

    WHERE SONG.ANO = ALBUM_NUM
                     *
ERROR at line 9:
ORA-00904: "ALBUM_NUM": invalid identifier

I don't know how else to do this question. So could anyone help?

EDIT: Now I've tried this and this is my new error

SELECT ALBUM.ANO
FROM ALBUM A
INNER JOIN PERFORM P
ON P.MNO != A.MNO
INNER JOIN SONG S
ON S.ANO = A.ANO
AND S.SNO = P.SNO;

SELECT ALBUM.ANO
       *
ERROR at line 1:
ORA-00904: "ALBUM"."ANO": invalid identifier

Upvotes: 0

Views: 41

Answers (2)

Josh Newlin
Josh Newlin

Reputation: 108

I got it thank you guys! I had to revise my search, but my edit worked (minus the error in the first line that was fixed thanks to sstan)

SELECT DISTINCT A.ANO
FROM ALBUM A
INNER JOIN PERFORM P
ON P.MNO != A.MNO
INNER JOIN SONG S
ON S.ANO = A.ANO
AND S.SNO = P.SNO
ORDER BY A.ANO;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is an Oracle restriction. The scope of a correlated subquery is only recognized one-deep in nesting. Your correlated subquery is too nested for Oracle to recognize the outer alias. Not all databases have this restriction.

Usually, I think this is a bad thing. But, following your query is rather difficult so a re-write is undoubtedly a simplification.

You don't describe the data well enough to suggest a concrete answer, but GROUP BY and HAVING come to mind as useful for this type of question.

In addition, you cannot refer to the column using an alias defined in the SELECT. You would have to use ALBUM.ANO.

Upvotes: 1

Related Questions