SQL连接多个表而不返回两次相同的连接ID

I have 3 (MYSQL) tables, A, B, and C.

我有3个(MYSQL)表,A,B和C.

What I want to do is something like this:

我想做的是这样的:

SELECT * FROM a
LEFT JOIN b ON b.a_id = a.id
LEFT JOIN c on c.a_id = a.id
WHERE a.id = 1;

I have 4 entries on the B table with B.a_id = 1:

我在B表上有4个条目,B.a_id = 1:

B.id | b.a_id
1    | 1
2    | 1
3    | 1
4    | 1

I also have 2 such entries on the C table

我在C表上也有2个这样的条目

C.id | C.a_id
10   | 1
11   | 1

The return gives 8 results:

返回给出了8个结果:

C.id | B.id
10    | 1
10    | 2
10    | 3
10    | 4
11    | 1
11    | 2
11    | 3
11    | 4

Is there a way to get the results to not repeat the same C and B objects, and return something more like this?

有没有办法让结果不重复相同的C和B对象,并返回更像这样的东西?

C.id | B.id
10    | 1
11    | 2
NULL  | 3
NULL  | 4

In other words, I want to say "give me everything that maps to A=1, but do not repeat the same C or B's twice". In my actual data, I'm joining closer to 10 tables, and with all the permutations of all the joined tables, the results are often thousands of rows long.

换句话说,我想说“给我一切映射到A = 1,但不要重复相同的C或B两次”。在我的实际数据中,我正在加入更接近10个表,并且对于所有连接表的所有排列,结果通常是数千行。

There is a similar question here Connecting Multiple Tables in SQL while Limiting Exponential Results , but I want to avoid inner selects to the extent possible. I feel this is a common enough use of a database that there should be

这里有类似的问题在SQL中连接多个表,同时限制指数结果,但我想尽可能避免内部选择。我觉得这是应该有的数据库的常见用法

3 个解决方案

#1

What you want is not simple SQL, and you should not expect it to be easy in relational databases.

你想要的不是简单的SQL,你不应该期望它在关系数据库中很容易。

I would advise you to put all the values in a single column, with another column specifying where they came from. This results in a large union all type of query:

我建议您将所有值放在一列中,另一列指定它们的来源。这导致了一个大型联合所有类型的查询:

select 'b' as which, b.id
from a join b on b.a_id = a.id and a.id = 1
union all
select 'c', c.id
from a join c on c_aid = b.id and a.id = 1
. . .

The results are not exactly what you want.

结果并不完全符合您的要求。

If you really want the data in separate columns, then you need to use a trick to assign a row number to each row. You can then aggregate the result by the row number. Something like this:

如果您确实希望将数据放在单独的列中,那么您需要使用技巧为每行分配行号。然后,您可以按行号聚合结果。像这样的东西:

select rn,
       MAX(case when which = 'b' then id end) as b,
       MAX(case when which = 'b' then id end) as c
from (select 'b' as which, b.id, @rn := @rn + 1 as rn
      from a join b on b.a_id = a.id and a.id = 1 cross join (select @rn := 0) const
      union all
      select 'c', c.id, @rn := @rn + 1 as rn
      from a join c on c_aid = b.id and a.id = 1 cross join (select @rn := 0) const
     ) t
group by rn

#2

That's a fundamental of using SQL to retrieve data; it retrieves data in complete rows, each with the same columns, as defined in the projection requested in the SELECT clause. Any time I run into issues like this with SQL, what has helped me is to ask myself three questions:

这是使用SQL检索数据的基础;它检索完整行中的数据,每个行具有相同的列,如SELECT子句中请求的投影中所定义。每当我用SQL遇到这样的问题时,对我有用的就是问自己三个问题:

What would the result rows I want look like? I.e., what columns would they have?

我想要的结果行是什么样的?即,他们会有哪些栏目?

How will the database know how to fill each column?

数据库将如何知道如何填充每列?

How will the database know which rows to return?

数据库将如何知道要返回哪些行?

Answering these questions for any given scenario has helped me to step back and see why a certain JOIN or GROUP BY was causing me trouble; generally, it's due to asking the database for something that it just doesn't have any concept of.

为任何给定的场景回答这些问题帮助我退后一步,看看为什么某个JOIN或GROUP BY给我带来了麻烦;一般来说,这是因为要求数据库提供一些它没有任何概念的东西。

In your case, retrieving rows of data, there is no way for the database to give you multiple rows from table B and multiple rows from table C without giving you the Cartesian product of those rows. If you don't want the duplicate data, you'll have to issue the SELECTs separately.

在您的情况下,检索数据行,数据库无法为表B和表C中的多行提供多行,而不会为您提供这些行的笛卡尔积。如果您不想要重复数据,则必须单独发出SELECT。

Phrased in answer to the questions above: Each row would have all the data from table A, and... uhm... what? There's no way to fit multiple rows from tables B and C into an arbitrary number of columns, so that won't work. There's no way to return short rows of data from B and C because every row is the same length. It'll have to return a full row for each row in B and each row in C, which gives you exactly the scenario you describe.

回答上面的问题:每一行都有表A中的所有数据,而且......呃......什么?无法将表B和C中的多行放入任意数量的列中,因此无效。没有办法从B和C返回短行数据,因为每行的长度相同。它必须为B中的每一行和C中的每一行返回一个完整的行,这样就可以准确地给出您描述的场景。

#3

Some one might use it, but seems easy....

有人可能会使用它,但似乎很容易....

could you try the following...

你可以试试以下......

Instead of Left Joint

而不是左关节

SELECT * FROM a
LEFT JOIN b ON b.a_id = a.id
LEFT JOIN c on c.a_id = a.id
WHERE a.id = 1;

Use the Following Inner Join

使用以下内部联接

SELECT * FROM a
INNER JOIN b ON b.a_id = a.id
INNER JOIN c on c.a_id = a.id
WHERE a.id = 1;

That should work...I think..

这应该工作......我想......

#1