CODEWITHSUNDEEP
sqloracle-databasejoinview
SavageCoder
SavageCoder

Reputation: 81

Create a view that joins data from other table multiple cells into one oracle sql

I need to create a view that combines two tables (game and developer) together and selects gameID gameName and gameDeveloper from the game table and repaces gameDeveloper with firstname and lastname (from developers table) into one column. gameDeveloper is the same as developerID, but it is not a foreign key yet on purpose. I want to select only firstname and lastname from the row that contains the matching developerID and combine them in one column in my new view. Heres what I have: 

CREATE TABLE game (
gameID varchar(3) PRIMARY KEY NOT NULL,
gamenameName varchar(30) NOT NULL,
gameDeveloper varchar(3) NOT NULL
);

INSERT INTO game VALUES (1,'RDR2',2);
INSERT INTO game VALUES (2,'GTAV',7);
INSERT INTO game VALUES (3,'PUBG',9);
/
CREATE TABLE developers (
    developerID varchar(3) PRIMARY KEY NOT 
NULL,
    fname varchar(20) NOT NULL,
    lname varchar(20) NOT NULL,
    gameID varchar(3) NOT NULL,
    CONSTRAINT fk_game
    FOREIGN KEY (gameID)
    REFERENCES game (GameID)
);

INSERT INTO developers VALUES 
(1,'Patrick','Kane',1);
INSERT INTO developers VALUES 
(2,'Jonathan','Toews',1);
INSERT INTO developers VALUES 
(3,'Alex','Debrincat',1);
INSERT INTO developers VALUES 
(4,'Andrew','Shaw',2);
INSERT INTO developers VALUES 
(5,'Alex','Nylander',2);
INSERT INTO developers VALUES 
(6,'Oli','Maata',2);
INSERT INTO developers VALUES 
(7,'Calvin','DeHaan',2);
INSERT INTO developers VALUES 
(8,'Brandon','Saad',3);
INSERT INTO developers VALUES 
(9,'Corey','Crawford',3);
INSERT INTO developers VALUES 
(10,'Connor','Murphy',3);
/

CREATE OR REPLACE VIEW chairs AS
    SELECT firstname, lastname
    FROM developer
    INNER JOIN

I'd like the final table to look something like this with the mapped and combined last cell but I am so lost on what to do.. I figured an inner join would be best?

enter image description here

Upvotes: 0

Views: 39

Answers (1)

user6235970
user6235970

Reputation:

You can do something like this - JOIN is the equivalent to INNER JOIN but you can be explicit.

CREATE VIEW chairs 

AS

SELECT 
 g.gameID 
,g.gamenameName 
,d.fname + ' ' + lname AS gameDeveloper 
FROM game g 
JOIN developers d 
    ON g.gameDeveloper = d. developerID

Upvotes: 2

Related Questions