sql, selecting using condition from another table-CodePudding

I have four tables, game(game_id, game_name, date_of_release), category(game_id, game_category), company(company_id, company_name) and belongs_to(game_id, company_id). I need to select game_id, game_name, and game_language where the company is 'Konami', I tried to do it like this

SELECT a.game_id, a.game_name, b.game_category, FROM game a 
    INNER JOIN category b ON a.game_id = b.game_id
    INNER JOIN company c ON  company_name = 'Konami' 
    INNER JOIN belongs_to d ON c.company_id = d.company_id

but it's selecting all the games regardless of the company name. So what went wrong?

CodePudding user response：

Your join is wrong.

SELECT
    ...
FROM
    game a
    INNER JOIN category b ON b.game_id = a.game_id
    INNER JOIN belongs_to d ON d.game_id = a.game_id
    INNER JOIN company c ON c.company_id = d.company_id
WHERE
    c.company_name = 'Konami'

CodePudding user response：

It seems thar you not really understand joins. You should use them to connect tables, in you case

INNER JOIN company c ON  company_name = 'Konami'

you connected row with info about Konami to all rows in previous tables. Try to think only about how you connect tables, not how you filter it while whriting join clause

So, you can fix it like this: just add condition thet shows how to connect tables

SELECT a.game_id, a.game_name, b.game_category
FROM game a 
    INNER JOIN category b ON a.game_id = b.game_id
    INNER JOIN belongs_to d ON a.game_id = d.game_id 
    INNER JOIN company c ON  c.company_id = d.company_id
WHERE company_name = 'Konami'

It's better to filter in where clause, bul this will work fine too, tho'

SELECT a.game_id, a.game_name, b.game_category
FROM game a 
    INNER JOIN category b ON a.game_id = b.game_id
    INNER JOIN belongs_to d ON a.game_id = d.game_id 
    INNER JOIN company c ON c.company_id = d.company_id
                            and company_name = 'Konami'