Invalid Identifier, cannot run JOIN query-CodePudding

Trying to run the following code to JOIN esrb rating and platform to the GAME table but failed:

select distinct ntdo_stage.title, ntdo_stage.meta_score, ntdo_stage.user_score, ntdo_stage.link, ntdo_stage.date2, ntdo_stage.platform, ntdo_stage.esrb_rating
      from ntdo_stage
      left join ntdo_platform 
      on (ntdo_platform.platform_id =ntdo_game.platform_id)
      left join ntdo_esrb_rating
      on (ntdo_esrb_rating.esrb_rating_id =ntdo_game.esrb_rating_id) 
      where title is not null

got the following error message:

ORA-00904: "NTDO_GAME"."PLATFORM_ID": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 159 Column: 38

DDL for ESRB_RATING TABLE:

CREATE TABLE ntdo_esrb_rating (
    esrb_rating    VARCHAR2(10),
    esrb_rating_id NUMBER NOT NULL
);

ALTER TABLE ntdo_esrb_rating ADD CONSTRAINT ntdo_esrb_rating_pk PRIMARY KEY ( esrb_rating_id );

DDL for PLATFORM TABLE:

CREATE TABLE ntdo_platform (
    platform_id   NUMBER NOT NULL,
    platform_name VARCHAR2(55)
);

ALTER TABLE ntdo_platform ADD CONSTRAINT ntdo_platform_pk PRIMARY KEY ( platform_id );

ALTER TABLE ntdo_game_developer
    ADD CONSTRAINT ntdo_game_dev_fk FOREIGN KEY ( developer_id )
        REFERENCES ntdo_developer ( developer_id );

DDL for the GAME TABLE:

CREATE TABLE ntdo_game (
    game_id              NUMBER NOT NULL,
    game_name            VARCHAR2(55),
    meta_score           VARCHAR2(10),
    user_score           VARCHAR2(10),
    link                 VARCHAR2(150),
    platform_id          NUMBER NOT NULL,
    esrb_rating_id       NUMBER,
    release_date         DATE,
    release_date_comment VARCHAR2(55)
);

ALTER TABLE ntdo_game ADD CONSTRAINT ntdo_game_pk PRIMARY KEY ( game_id );

DDL for the STAGE TABLE:

CREATE TABLE "MASY_CW3956"."NTDO_STAGE" (
    "META_SCORE" NUMBER(38,0), 
    "TITLE" VARCHAR2(256 BYTE), 
    "PLATFORM" VARCHAR2(26 BYTE), 
    "DATE2" VARCHAR2(50 BYTE), 
    "USER_SCORE" NUMBER(38,1), 
    "LINK" VARCHAR2(256 BYTE), 
    "ESRB_RATING" VARCHAR2(26 BYTE), 
    "DEVELOPERS" VARCHAR2(128 BYTE), 
    "GENRES" VARCHAR2(128 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

I have already made sequence for platform_id, esrb_rating_id, and game_id.

CodePudding user response：

Your join clause (ntdo_platform.platform_id =ntdo_game.platform_id) references the ntdo_game table, but you are not joining to that table in this query. You need to include that table in your from clause.

If you have a game_id column in the ntdo_stage table, your query would look like this:

select distinct   
    ntdo_stage.title
   ,ntdo_stage.meta_score
   ,ntdo_stage.user_score
   ,ntdo_stage.link
   ,ntdo_stage.date2
   ,ntdo_stage.platform
   ,ntdo_stage.esrb_rating
from   
    ntdo_stage
    left join
    ntdo_game on NTDO_STAGE.game_id = ntdo_game.game_id
    left join
    ntdo_platform on (ntdo_platform.platform_id = ntdo_game.platform_id)
    left join
    ntdo_esrb_rating on(ntdo_esrb_rating.esrb_rating_id = ntdo_game.esrb_rating_id)
where title is not null;

CodePudding user response：

You are getting an error because your query is using the table ntdo_stage which does not seem to exist (at least in the tables that you gave us).

You will also receive an error about the title column because none of the tables have a title column.

It seems like you are mixing up some of your tables.