MySQL - 多重联合

问题描述

(任何可以解释我如何在 SQL 和 SQLAlchemy 中执行此操作的人的额外互联网.)

我有三张桌子是这样设置的:

I have three tables set up like so:

**Bottom:**
id
name
middle_id

**Middle:**
id
name
top_id

**Top:**
id
name

每个底部都有一个中间，每个中间都有一个顶部.我想对数据库进行搜索，以便当用户输入查询时，它将搜索底部、中间和顶部.如果有人搜索恰好在 Top 中的项目，它将返回所有与 Top 相关的 Bottom.*，通过 Middle.

Each bottom has a middle, and each middle has a top. I want to do a search on a database so that when the user types in their query, it will search Bottom, Middle, and Top. If someone searches for an item that happens to be in Top, it will return all the Bottom.* that are related to Top by going through Middle.

这是一个在两个层面上工作的查询:

Here's a query that works on two levels:

SELECT *
FROM   Bottom
WHERE  name LIKE '%query%'

UNION

SELECT Bottom.*
FROM   Middle JOIN Bottom ON Bottom.middle_id = Middle.id
WHERE  Middle.name LIKE '%query%'

我如何扩展它以便它也可以搜索 Top WHERE Top.name LIKE '%query%'，但不是返回 Middle.*，而是返回 Bottom.*?这是一些不起作用的 SQL，但我认为解释了我想要实现的目标:

How do I extend this so that it can also search Top WHERE Top.name LIKE '%query%', but instead of returning Middle.*, it returns Bottom.*? Here's some SQL that doesn't work, but I think explains what I'm trying to achieve:

SELECT *
FROM   Bottom
WHERE  name LIKE '%query%'

UNION

SELECT Bottom.*
FROM   Middle JOIN Bottom ON Bottom.middle_id = Middle.id
WHERE  Middle.name LIKE '%query%'

UNION

SELECT Bottom.*
FROM   Top JOIN Middle ON Middle.top_id = Top.id
WHERE  Top.name LIKE '%query%'

换句话说，如果我在Bottom中有一个条目与Middle中的条目相关，而Middle中的条目与Top中的条目相关，如果我在Top中搜索适当的名称，它不会返回与其相关的中间条目，但与相关中间条目相关的底部条目.

In other words, if I have an entry in Bottom that is related to an entry in Middle, and that entry in Middle is related to an entry in Top, if I search Top for the appropriate name, it will return not the Middle entry that is related to it, but the Bottom entry that is related to the related Middle entry.

这是一个例子:

数据:

**Bottom**
id        1
name      Gus Fring
middle_id 1

**Middle**
id        1
name      Jesse Pinkman
top_id    1

**Top**
id        1
name      Walter White

如果我搜索Walter White"，它不会返回中间关系(Jesse Pinkman)，而是返回底部关系到中间关系的结果(Gus Fring).

If I search for "Walter White" it will return not the Middle relation (Jesse Pinkman), but rather the Bottom relation's to the Middle relation's result (Gus Fring).

这个问题已经困扰我一段时间了，我非常感谢您的帮助.提前致谢！:)

This issue has been troubling me for a while now and I'd really appreciate any help. Thanks in advance! :)

推荐答案

试试这个

select
  b.id,
  b.name,
  b.middle_id,
  m.top_id,
  m.name,
  t.name
from bottom as b
  left join (select  id,`name`, top_id  from Middle) as m  on m.id = b.middle_id
  left join (select  id,`name`from Top) as t   on t.id = m.top_id

这篇关于MySQL - 多重联合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！