How to write query that join the 2 tables?-CodePudding

Using mysql. Database sakila. Having two tables films and language. Film table has two foreign keys language_id and original_language_id that connects to language_id primary key in language. So what I want is to learn how to get film, it's language and original language from such structure.

enter image description here

The query will display film in its language, without original.

SELECT 
    film.title, film.description, film.release_year, language.*
FROM
    film
        INNER JOIN
    language ON film.language_id = language.language_id;

CodePudding user response：

If I understood correctly, you want to select the films having language other than original language. I think a where clause will serve the purpose.

SELECT 
    film.title, film.description, film.release_year, language.*
FROM
    film INNER JOIN language 
         ON film.language_id = language.language_id;
    where film.language_id <> film.original_language_id

Or, If you just want to display language information for both the language you can follow one of below query (using subquery):

SELECT  film.title, film.description, film.release_year, 
        (select name from language l where l.language_id=film.language_id) language_name,
        (select name from language l where l.language_id=film.original_language_id) original_language_name
FROM film

CodePudding user response：

Maybe you can try this

SELECT a.title, a.description, a.release_year, b.language_id, b.name, b.last_update
FROM
film a
INNER JOIN
language b ON a.language_id = b.language_id;