无法编写三个表的查询。

huangapple go评论64阅读模式
英文:

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
-- |-----------|-----------|----------|

huangapple
  • 本文由 发表于 2023年6月19日 06:21:10
  • 转载请务必保留本文链接:https://go.coder-hub.com/76502738.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定