需要查询帮助（使用连接）

I have a three tables:

我有三张桌子:

Checkbook table holds what I need to get
Receipts table holds a link to a owner table
Owner table knows what checkbook it is linked to

支票簿表保存我需要获取的内容收据表包含指向所有者表的链接所有者表知道它链接到哪个支票簿

I need to get the checkbooks only when there are rows in the receipts that are linked to it (through the Owner table). I don't know exactly how to do this, and it kinda seems circular. This is what I've tried:

我只需要在收据中有行链接(通过所有者表)时才能获得支票簿。我不确切知道如何做到这一点,它似乎有点循环。这就是我尝试过的:

SELECT chk.ID, chk.Description FROM tblCheckbook chk
LEFT JOIN tblOwner o
ON r.OwnerID = o.ID
INNER JOIN tblCashReceipts r
ON chk.ID = o.CheckbookID

But sql server complains that "The multi-part identifier "r.OwnerID" could not be bound."
What do I need to do to get this to work?

但是sql server抱怨“多部分标识符”r.OwnerID“无法绑定”。我需要做些什么才能让它发挥作用?

4 个解决方案

#1

Each join has a on clause that describes the relation. You just have to put the relations with the correct joins.

每个连接都有一个描述关系的on子句。你只需要将关系与正确的连接放在一起。

There is no point in using a left join here as you are using an inner join in the second step. That only causes a larger set for the database to work with to get the same result.

在此处使用左连接没有意义,因为您在第二步中使用内连接。这只会导致数据库使用更大的集合来获得相同的结果。

select c.ID, c.Description
from tblCheckBook c
inner join tlbOwner o on o.CheckbookID = c.ID
inner join tblCashReceipts r on r.OwnerID = o.ID

#2

At the point you do the LEFT JOIN, the definition of r (tblCashReceipts) hasn't been encountered yet. You'll probably want something like:

在执行LEFT JOIN时,尚未遇到r(tblCashReceipts)的定义。你可能想要这样的东西:

SELECT chk.ID, chk.Description FROM tblCheckbook chk
LEFT JOIN tblOwner o
ON chk.ID = o.CheckbookID
INNER JOIN tblCashReceipts r
ON o.ID = r.OwnerID

#3

I think you're almost there, you just have your join conditions switched around. Try this:

我想你几乎就在那里,你只需要改变你的连接条件。试试这个:

SELECT chk.ID, chk.Description FROM tblCheckbook chk
LEFT JOIN tblOwner o ON o.CheckbookID = chk.ID 
INNER JOIN tblCashReceipts r ON r.OwnerID = o.ID

Or:

SELECT chk.ID, chk.Description FROM tblCheckbook chk
LEFT JOIN tblOwner o ON o.CheckbookID = chk.ID 
LEFT JOIN tblCashReceipts r ON r.OwnerID = o.ID 
WHERE r.OwnerID IS NOT NULL

So what hapens if there are multiple reciepts for the same Owner? Using this query, you would return a checkbook for each reciept, which may be what you want, but it doesn't sound like it. You may want to throw a DISTINCT on there as well.

那么,如果同一所有者有多个收据,那会是什么?使用此查询,您将返回每个收据的支票簿,这可能是您想要的,但听起来不像。您可能也想在那里抛出DISTINCT。

#4

Do receipts know what checkbook they belong to?

收据知道他们属于哪个支票簿?

Join checkbooks through the receipts table.

通过收据表加入支票簿。

Owner -> Receipts -> Checkbook

所有者 - >收据 - >支票簿

#1