Need help with a query (using joins)

Question

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?

Answer 1

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

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

Answer 2

Do receipts know what checkbook they belong to?

Join checkbooks through the receipts table.

Owner -> Receipts -> Checkbook

Answer 3

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

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

Answer 4

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.