sql - Query with double joins

Question

My entities are: Characters - Stats - Cards

The relationship between then is simple:

Characters have one-to-one for Stats

Characters have one-to-many for Cards

The expected query result is :

card.id, character.name, characters.thumbnail, characters.type, stats.overall

P.S: Cards and Stats have not direct relation, but Characters have a stat_id column

Resuming

How it's possible to in a single query achieve the expected result?

My attempts:

SELECT 
    cards.id, characters.thumbnail, characters.type, characters.name
FROM 
    cards
INNER JOIN 
    characters 
ON cards.character_id=characters.id

Result

cards.id, characters.thumbnail, characters.type, characters.name

Attempt to get stats.overall:

SELECT 
    cards.id, characters.thumbnail, characters.type, characters.name, stats.overall
FROM 
    cards, characters
INNER JOIN 
    characters 
ON cards.character_id=characters.id
INNER JOIN 
    stats 
ON characters.stat_id=stats.id

Result

error: table name "characters" specified more than once

question from:https://stackoverflow.com/questions/65602938/query-with-double-joins

Answer 1

you are joining twice to characters table , please avoid using implicit joins ( using , to join tables ) , always use explicit join :

SELECT
    cards.id
    , characters.thumbnail
    , characters.type
    , characters.name
    , stats.overall
FROM
    cards
    INNER JOIN characters
        ON cards.character_id = characters.id
    INNER JOIN stats
        ON characters.stat_id = stats.id