时间戳

首页 > 解决方案 > 如何使用 SQLite 通过 ID 将表连接在一起？

sql - 如何使用 SQLite 通过 ID 将表连接在一起？

问题描述

我在连接部分表格时遇到问题。我希望将人们的名字和姓氏以及他们感兴趣的任何东西结合在一起。我收到此错误消息：“[1] [SQLITE_ERROR] SQL 错误或缺少数据库（不明确的列名：pi.PersonID）”

CREATE TABLE people (
PersonID INTEGER PRIMARY KEY AUTOINCREMENT,
FirstName VARCHAR(100),
LastName VARCHAR(100)
);

INSERT INTO people (FirstName, LastName)
VALUES ('Walter', 'White'),
       ('Jesse', 'Pinkman'),
       ('Saul', 'Goodman');

SELECT * FROM people;

CREATE TABLE interests (
     InterestID INTEGER PRIMARY KEY AUTOINCREMENT,
     Interest VARCHAR(100)
);

INSERT INTO interests (Interest)
values ('Swimming'),
       ('Basketball'),
       ('Running');

SELECT * FROM interests;

CREATE TABLE persons_interests (
    PersonID INTEGER,
    InterestID INTEGER,
    PRIMARY KEY (PersonID, InterestID),
    FOREIGN KEY (PersonID) REFERENCES people,
    FOREIGN KEY (InterestID) REFERENCES interests
);

DROP TABLE persons_interests;

INSERT INTO persons_interests (PersonID, InterestID)
VALUES (1, 3),
   (2, 2),
   (3, 3);

SELECT * FROM persons_interests;

SELECT FirstName, LastName, Interest FROM people p, interests i
JOIN persons_interests pi on p.PersonID = pi.PersonID
JOIN persons_interests pi on i.Interest = pi.InterestID;

标签： sqlsqliteforeign-keysinner-join

解决方案

不要混合隐式和显式连接！你似乎想要：

select p.firstname, p.lastname, i.interest 
from people p
inner join persons_interests pi on pi.personid = p.personid
inner join interests i on i.interestid = pi.interestid;

from在这里，每个表在子句中只出现一次，并带有相关的连接条件。

推荐阅读