如何删除不必要的连接，更新哪里的条件

I've got three tables in a MySql database that I'm joining in a query to get id/value pairs.

我在MySql数据库中有三个表，我将它们加入查询以获得id/值对。

   |    A     |       B      |     C         |
   | -------- |--------------|---------------|
   |   id     |  id          |  id           |
   |   name   |  fooId       |  attributeId  |
   |   desc   |  value       |  displayIndex |
   |   ...    |  attributeId |  ...          |

What I have now is:

我现在拥有的是:

SELECT C.id, B.value
  FROM  A, B, C
  WHERE A.id = B.attributeId
    AND A.id = C.attributeId
    AND B.fooId = 25
  ORDER BY C.displayIndex

So basically we're joining B and C through A. It used to be that an entry in the C table had to have a corresponding (parent) entry in the A table. However, that will no longer be the case. The C table will still be MOSTLY controlled by the A table, however, there are some instances when we need a stand alone (always on) entry in the C table.

所以基本上我们把B和C和a连接在一起。C表中的一个条目必须在a表中有一个对应的(父)条目。然而，情况将不再如此。C表仍然主要由A表控制，但是，当我们在C表中需要一个独立的(始终处于on状态)条目时，存在一些实例。

EDIT

编辑

I want all the records from B and C that match on attributeId but I also want any record where C.attributeId = -1. Can someone help with what I'd need to do with this query?

我想要所有来自B和C的与attributeId匹配的记录，但我也想要任何包含C的记录。attributeId = 1。有人能帮我处理这个查询吗?

Edit #2

编辑# 2

Based on feedback and suggestions you guys have made and some googling I now have this:

基于你们的反馈和建议以及一些谷歌搜索，我现在有了:

(SELECT C.id, B.value, C.displayIndex
  FROM  B, C
  WHERE B.attributeId = C.attributeId
    AND B.fooId = 25)

UNION

(SELECT C.id, null, C.displayIndex
  FROM C
  WHERE C.attributeId = -1)

ORDER BY 3

Is there a better what to do this? Are there any problems with UNION?

有更好的办法吗?工会有什么问题吗?

2 个解决方案

#1

I've updated my answer to address the edits from the OP.

我已经更新了我的答案来处理OP的编辑。

This will return all records where the attributeId for tables B and C match,
with B.fooId = 25, OR C.attributeId = -1.
When C.attributeId = -1 and there is no match in table B, NULL will be returned in place of B.Value, which appears to be acceptable based on the Edit #2 from the OP

这将返回表B和C与B匹配的所有记录。fooId = 25，或C。attributeId = 1。当C。attributeId = -1且表B中没有匹配项，将返回NULL代替B。值，根据OP中的编辑#2可以接受

SELECT C.Id, B.Value, C.displayIndex
FROM C 
LEFT JOIN B ON C.attributeId = B.attributeId
WHERE B.fooId = 25
   OR C.attributeId = -1
ORDER BY C.DisplayIndex ASC

#2

You're probably getting a Cartesian product.

你可能会得到一个笛卡尔积。

Like Adam Wenger said you'll need to inner join them. Inner joins will return rows when there is at least 1 match in all joined tables.

就像亚当·温格说的，你需要在内心加入他们。当所有已连接表中至少有一个匹配时，内部连接将返回行。

If you need to ensure that the result is also in Table A, you're going to need to join that one too:

如果您需要确保结果也在表A中，那么您也需要加入其中:

SELECT c.Id, b.Value
FROM B 
INNER JOIN A ON B.attributeId = A.ID
INNER JOIN C ON B.attributeId = C.attributeId
WHERE B.fooId = 25
   AND C.attributeId = -1
ORDER BY C.DisplayIndex

#1