How do I fix SQL retrieving from the wrong given ID-CodePudding

I'm trying to connect to link two tables together in my SQL database. I got it to sort of work but my program is using the wrong ID column. It should be using the GenreID column instead but I don't know how to fix that.

I have a feeling its a logical error but I don't know how to fix it

import sqlite3
conn = sqlite3.connect("ShowInformation.db")
c = conn.cursor()
c.execute('''SELECT name,GenreName,GenreID  
FROM Genres
NATURAL JOIN Animes''')
item = c.fetchall()

print (item)

conn.commit()
conn.close()

It returns this

[('Pokemon', 'Action', 1), ('Minecraft', 'Work Life', 3), ('Pokemon', 'Ecchi', 2)]

The program is using the ID instead of GenreID to get the Genre from the Genres Table

SQL Code - Anime's Table


CREATE TABLE "Animes" (
    "ID"    INTEGER,
    "Name"  TEXT,
    "GenreID"   INTEGER,
    FOREIGN KEY("GenreID") REFERENCES "Genres"("ID"),
    PRIMARY KEY("ID" AUTOINCREMENT)
);

Genres Table Code:

CREATE TABLE "Genres" (
    "ID"    INTEGER,
    "GenreName" TEXT,
    PRIMARY KEY("ID" AUTOINCREMENT)
);

Table content:

CodePudding user response：

This is because of the NATURAL JOIN, you need to use a different join such as an INNER JOIN(Shortly using JOIN implicitly means INNER JOIN). Try changing your SQLite query to this:

SELECT name, GenreName, GenreID  
  FROM Genres AS g
  JOIN Animes AS a
    ON a.GenreID = g.ID;