CODEWITHSUNDEEP
sql
mk-pawn
mk-pawn

Reputation: 31

sql, selecting using condition from another table

I have four tables,

game(game_id, game_name, date_of_release), 
category(game_id, game_category), 
company(company_id, company_name),
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?

Upvotes: 0

Views: 56

Answers (2)

KaySa
KaySa

Reputation: 71

It seems that 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 writing join clause

So, you can fix it like this: just add condition that 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, but this will work fine too

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'

Upvotes: 1

Richard Deeming
Richard Deeming

Reputation: 31198

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'

Upvotes: 2

Related Questions