英文:
Can't write a query for three tables
问题
我需要编写以下查询。假设我们有以下表结构:
main_table
:
id 主键
column1,
column2,
column3
table_1
:
id 主键
main_table_id 引用 main_table 非空,
column1,
column2,
column3
table_2
:
id 主键
table_1_id 引用 table1 非空,
necessary_column
我需要从main_table
中获取具有table_2.necessary_column
中给定值的数据,通过与table_1
的链接。我该如何做?
我已经为2个表编写了查询,但我不知道如何在3个表中执行此操作。
英文:
I need to write the following query. Suppose we have a table structure like this:
main_table
:
id PRIMARY KEY
column1,
column2,
column3
table_1
:
id PRIMARY KEY
main_table_id REFERENCES main_table NOT NULL,
column1,
column2,
column3
table_2
:
id PRIMARY KEY
table_1_id REFERENCES table1 NOT NULL,
necessary_column
I need to get data from the main_table
that has a given value in table_2.necessary_column
through a link to the table_1
. How can I do this?
I wrote a query for 2 tables, but I don't know how to do this with 3 tables.
答案1
得分: 1
从
main_table
中获取具有necessary_column
列的数据,通过与table_1
的链接从table_2
中获取。
有许多方法。通过使用 EXISTS
查询来演示,以便从 main_table
获取唯一行:
SELECT *
FROM main_table m
WHERE EXISTS (
SELECT -- 可以为空
FROM table_1 t1
JOIN table_2 t2 ON t2.table_1_id = t1.id
WHERE t1.main_table_id = m.id
AND t2.necessary_column = $input
);
您还可以将所有三个表连接起来,但这可能会导致行数增加,并且您可能需要添加一个昂贵的 DISTINCT
:
SELECT m.* -- 或:DISTINCT m.*
FROM main_table m
JOIN table_1 t1 ON t1.main_table_id = m.id
JOIN table_2 t2 ON t2.table_1_id = t1.id
WHERE t2.necessary_column = $input;
注意:上述内容仅为代码部分的翻译。
英文:
> get data from the main_table
that has a column necessary_column
from table_2
through a link to the table_1
There are many ways. Demonstrating a query with EXISTS
, so that you get unique rows from main_table
:
SELECT *
FROM main_table m
WHERE EXISTS (
SELECT -- can be empty
FROM table_1 t1
JOIN table_2 t2 ON t2.table_1_id = t1.id
WHERE t1.main_table_id = m.id
AND t2.necessary_column = $input
);
You could also just join all three tables, but that might multiply rows, and you might have to add another expensive DISTINCT
:
SELECT m.* -- or: DISTINCT m.*
FROM main_table m
JOIN table_1 t1 ON t1.main_table_id = m.id
JOIN table_2 t2 ON t2.table_1_id = t1.id
WHERE t2.necessary_column = $input;
答案2
得分: 0
问题描述不太清楚,所以很难确定确切的答案。但似乎你需要更好地理解内连接(inner join)和左连接(left join)之间的区别,以及我们如何链接这些操作。
让我们尝试使用以下结构来更清晰地表述:
创建这些表并填充一些数据
-- 创建
CREATE TABLE USER (
user_id INTEGER PRIMARY KEY,
user_name TEXT NOT NULL
);
CREATE TABLE DOG (
dog_id INTEGER PRIMARY KEY,
dog_name TEXT NOT NULL,
ower_id INTEGER,
FOREIGN KEY (ower_id) REFERENCES USER(user_id)
);
CREATE TABLE TOY (
toy_id INTEGER PRIMARY KEY,
dog_id INTEGER,
toy_name TEXT NOT NULL,
FOREIGN KEY (dog_id) REFERENCES DOG(dog_id)
);
-- 插入
INSERT INTO USER VALUES (1001, 'Anna'); -- Anna有2只狗
INSERT INTO USER VALUES (1002, 'Bob'); -- Bob有1只狗
INSERT INTO USER VALUES (1003, 'Carl'); -- Carl没有狗
INSERT INTO DOG VALUES (2001, 'Snoopy' , 1001); -- Snoopy属于Anna,有2个玩具
INSERT INTO DOG VALUES (2002, 'Fluffy' , 1001); -- Fluffy属于Anna,有1个玩具
INSERT INTO DOG VALUES (2003, 'Snow Ball', 1002); -- Snow Ball属于Bob,没有玩具
INSERT INTO DOG VALUES (2004, 'Zorro', NULL); -- Zorro有一个玩具但没有主人
INSERT INTO TOY VALUES (3001, 2001, 'Bone'); -- Bone属于Snoopy
INSERT INTO TOY VALUES (3002, 2001, 'Stick'); -- Stick属于Snoopy
INSERT INTO TOY VALUES (3003, 2002, 'Ball'); -- Ball属于Fluffy
INSERT INTO TOY VALUES (3004, 2004, 'Mask'); -- Mask属于Zorro
查询
如果你想要链式连接这三个表之间的关系,需要所有的连接,你可以在任何方向上执行。
换句话说,这个查询的结果:
SELECT
user_name,
dog_name,
toy_name
FROM TOY -- 遍历所有玩具
INNER JOIN DOG ON DOG.dog_id = toy.dog_id -- 然后查找拥有这些玩具的狗(必需的)
INNER JOIN USER ON user.user_id = dog.ower_id; -- 然后查找拥有这些狗的用户(必需的)
-- |-----------|----------|----------|
-- | user_name | dog_name | toy_name |
-- |-----------|----------|----------|
-- | Anna | Snoopy | Bone |
-- | Anna | Snoopy | Stick |
-- | Anna | Fluffy | Ball |
-- |-----------|----------|----------|
与以下查询的结果相同:
SELECT
user_name,
dog_name,
toy_name
FROM USER -- 遍历所有用户
INNER JOIN DOG ON dog.ower_id = user.user_id -- 然后查找用户拥有的狗(必需的)
INNER JOIN TOY ON toy.dog_id = dog.dog_id; -- 然后查找狗拥有的玩具(必需的)
-- |-----------|----------|----------|
-- | user_name | dog_name | toy_name |
-- |-----------|----------|----------|
-- | Anna | Snoopy | Bone |
-- | Anna | Snoopy | Stick |
-- | Anna | Fluffy | Ball |
-- |-----------|----------|----------|
但是,当我们使用左连接链式查询时,链式查询的顺序会产生差异。正如你可以在以下查询中看到的:
SELECT
user_name,
dog_name,
toy_name
FROM TOY -- 遍历所有玩具
LEFT JOIN DOG ON DOG.dog_id = toy.dog_id -- 然后查找拥有这些玩具的狗(可选的)
LEFT JOIN USER ON user.user_id = dog.ower_id; -- 然后查找拥有这些狗的用户(可选的)
-- |-----------|----------|----------|
-- | user_name | dog_name | toy_name |
-- |-----------|----------|----------|
-- | Anna | Snoopy | Bone |
-- | Anna | Snoopy | Stick |
-- | Anna | Fluffy | Ball |
-- | NULL | Zorro | Mask | -- 注意Zorro没有主人
-- |-----------|----------|----------|
SELECT
user_name,
dog_name,
toy_name
FROM USER -- 遍历所有用户
LEFT JOIN DOG ON dog.ower_id = user.user_id -- 然后查找用户拥有的狗(可选的)
LEFT JOIN TOY ON toy.dog_id = dog.dog_id; -- 然后查找狗拥有的玩具(可选的)
-- |-----------|-----------|----------|
-- | user_name | dog_name | toy_name |
-- |-----------|-----------|----------|
-- | Anna | Snoopy | Bone |
-- | Anna | Snoopy | Stick |
-- | Anna | Fluffy | Ball |
-- | Bob | Snow Ball | NULL | -- 注意Snow Ball没有玩具
-- | Carl | NULL | NULL | -- 注意Carl没有狗
-- |-----------|-----------|----------|
英文:
The question description is not very clear. So, it is hard to answer for sure. But, it seems that you need to better understand the difference between inner join and left join and that we can chain these operations.
Let's try to use some more clear as the structure below:
Creating these tables and populating with some data
-- create
CREATE TABLE USER (
user_id INTEGER PRIMARY KEY,
user_name TEXT NOT NULL
);
CREATE TABLE DOG (
dog_id INTEGER PRIMARY KEY,
dog_name TEXT NOT NULL,
ower_id INTEGER,
FOREIGN KEY (ower_id) REFERENCES USER(user_id)
);
CREATE TABLE TOY (
toy_id INTEGER PRIMARY KEY,
dog_id INTEGER,
toy_name TEXT NOT NULL,
FOREIGN KEY (dog_id) REFERENCES DOG(dog_id)
);
-- insert
INSERT INTO USER VALUES (1001, 'Anna'); -- Anna has 2 dogs
INSERT INTO USER VALUES (1002, 'Bob'); -- Bob has 1 dog
INSERT INTO USER VALUES (1003, 'Carl'); -- Carl does not have dogs
INSERT INTO DOG VALUES (2001, 'Snoopy' , 1001); -- Snoopy belongs to Anna and have 2 toys
INSERT INTO DOG VALUES (2002, 'Fluffy' , 1001); -- Fluffy belongs to Anna and have 1 toy
INSERT INTO DOG VALUES (2003, 'Snow Ball', 1002); -- Snow Ball belongs to Bob and have no toys
INSERT INTO DOG VALUES (2004, 'Zorro', NULL); -- Zorro has one toy but no owner
INSERT INTO TOY VALUES (3001, 2001, 'Bone'); -- Bone belongs to Snoopy
INSERT INTO TOY VALUES (3002, 2001, 'Stick'); -- Stick belongs to Snoopy
INSERT INTO TOY VALUES (3003, 2002, 'Ball'); -- Ball belongs to Fluffy
INSERT INTO TOY VALUES (3004, 2004, 'Mask'); -- Mask belongs to Zorro
Queries
If you want to chain the relationship between these three tables that require all the connections, you can do that in any of the directions.
In other words, the result of this query:
SELECT
user_name,
dog_name,
toy_name
FROM TOY -- go to all toys
INNER JOIN DOG ON DOG.dog_id = toy.dog_id -- then search for the dogs owners of the toys (required)
INNER JOIN USER ON user.user_id = dog.ower_id; -- then search for the users owners of the dogs
-- |-----------|----------|----------|
-- | user_name | dog_name | toy_name |
-- |-----------|----------|----------|
-- | Anna | Snoopy | Bone |
-- | Anna | Snoopy | Stick |
-- | Anna | Fluffy | Ball |
-- |-----------|----------|----------|
Is the same the result of this one:
SELECT
user_name,
dog_name,
toy_name
FROM USER -- go to all users
INNER JOIN DOG ON dog.ower_id = user.user_id -- then go to the users dogs (required)
INNER JOIN TOY ON toy.dog_id = dog.dog_id; -- then go to the dogs toys (required)
-- |-----------|----------|----------|
-- | user_name | dog_name | toy_name |
-- |-----------|----------|----------|
-- | Anna | Snoopy | Bone |
-- | Anna | Snoopy | Stick |
-- | Anna | Fluffy | Ball |
-- |-----------|----------|----------|
But, when we chain the queries using left join, the order of the chain makes a difference. As you can see in the following queries:
SELECT
user_name,
dog_name,
toy_name
FROM TOY -- go to all toys
LEFT JOIN DOG ON DOG.dog_id = toy.dog_id -- then search for the dogs owners of the toys (optional)
LEFT JOIN USER ON user.user_id = dog.ower_id; -- then search for the users owners of the dogs (optional)
-- |-----------|----------|----------|
-- | user_name | dog_name | toy_name |
-- |-----------|----------|----------|
-- | Anna | Snoopy | Bone |
-- | Anna | Snoopy | Stick |
-- | Anna | Fluffy | Ball |
-- | NULL | Zorro | Mask | -- Notice that Zorro has no owner
-- |-----------|----------|----------|
SELECT
user_name,
dog_name,
toy_name
FROM USER -- go to all users
LEFT JOIN DOG ON dog.ower_id = user.user_id -- then go to the users dogs (optional)
LEFT JOIN TOY ON toy.dog_id = dog.dog_id; -- then go to the dogs toys (optional)
-- |-----------|-----------|----------|
-- | user_name | dog_name | toy_name |
-- |-----------|-----------|----------|
-- | Anna | Snoopy | Bone |
-- | Anna | Snoopy | Stick |
-- | Anna | Fluffy | Ball |
-- | Bob | Snow Ball | NULL | -- Notice that Snow Ball have no toys
-- | Carl | NULL | NULL | -- Notice that Carl have no dogs
-- |-----------|-----------|----------|
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论