Creating view with 2 foreign keys of one table

July 12, 2023

releases.studio_id and releases.publisher_id both refer to companies.id. I tried

CREATE VIEW v_releases AS
SELECT releases.id AS 'id', 
 games.name AS name,
 companies.name AS studio,
 companies.name AS publisher
FROM releases
Left JOIN games ON releases.game_id = games.id
Left JOIN companies ON releases.studio_id = companies.id 
                   AND releases.publisher_id = companies.id;

but the resulting view shows mostly null in studio_id and publisher_id where each record should contain references. Testing

CREATE VIEW v_releases AS
SELECT releases.id AS 'id', 
 games.name AS name,
 companies.name AS studio,
 companies.name AS publisher
FROM releases
Left JOIN games ON releases.game_id = games.id
Left JOIN companies ON releases.studio_id = companies.id;

results in strangely having publisher_id as an exact copy of the studio_id column, while the studio_id column has all correct data. How to create a view with correct references from these two foreign keys?

>Solution :

When they are different you need to join companies twice, to catch each name

CREATE VIEW v_releases AS
SELECT releases.id AS 'id', 
 games.name AS name,
 c1.name AS studio,
 c2.name AS publisher,
FROM releases
Left JOIN games ON releases.game_id = games.id
Left JOIN companies c1 ON releases.studio_id = c1.id 
Left JOIN companies c2 ON  releases.publisher_id = c2.id;