How to do a SQL query using columns from a related table?

I've got three related SQL tables, simplified they look like this:

ShopTable
  [ShopID]

ShelfTable
  [ShelfID]
  [ShopID]

InventoryTable
  [ShelfID]
  [Value]

[ShopID] and [ShelfID] are relations. Now what I want to do is get the SUM of [Value] for one [ShopID], but this obviously won't work since [ShopID] ain't part of In开发者_JAVA百科ventoryTable:

SELECT SUM([Value]) WHERE [ShopID] = '1'

How do I have to write the query to filter the InventoryTable using the ShopID?

---

SELECT  SUM(i.value)
FROM    shelfTable s
JOIN    inventoryTable i
ON      i.shelfId = s.shelfId
WHERE   s.shopId = 1

---

This is a fundamental question about relations between tables, so I'll provide some detail, hoping that you can use some of these ideas when writing SQL queries in the future.

Let's start with one basic thing first. [ShopID] could refer to two different but related columns, one in [ShopTable] and one in [ShelfTable]. The same things applies to [ShelfID]. It's useful to always specify the table.

You describe [ShopID] and [ShelfID] as "relations." As Damien_The_Unbeliever has commented, those columns are, in fact, two pairs of primary and foreign keys. That is, [ShelfTable].[ShelfID] identifies a "shelf" record, and [InventoryTable].[ShelfID] relates an "inventory item" (whatever that is) to a "shelf." (It's not always possible to interpret rows in a database this naively, but I'm willing to guess I'm not too far off from reality.)

Likewise, each "shelf" belongs to one "shop," and [ShelfTable].[ShopID] refers to that specific "shop." Notice that because we have the value of [ShopID] already (I'll call it "@MyShopID"), we don't even need the [ShopTable] here. We can just use [ShelfTable].[ShopID] to filter for the "shelves" we're interested in.

You're asking to get the sum total of [InventoryTable].[Value] for one [ShopID] value, but [ShopID] doesn't show up in [InventoryTable]. That's where your (inner) join comes into play. You know that you'll be adding up values from [InventoryTable], but you've got to specify the particular "shop." You specify @MyShopID for [ShelfTable].[ShelfID], which will do your filtering in [InventoryTable] for you.

One final thing before composing the query. I'm assuming that you haven't oversimplified your tables too much, and that [Value] is the total value of each "inventory item," and not just a unit value. If it wasn't, we'd have to multiply values by quantities, etc., but I'll let you check your own work here.

So, here's what we do:

• We select FROM the [InventoryTable]
• but we INNER JOIN to the [ShelfTable] on [ShelfID] from both tables
• and we only want "shelves" from one "shop," i.e. WHERE [ShelfTable].[ShopID] = @MyShopID
• and then we SELECT the SUM([InventoryTable].[Value])

and we're done. In SQL, let's remove the brackets, provide some table aliases, and we'll get a query that looks like this:

SELECT SUM(inv.Value)
FROM InventoryTable AS inv
INNER JOIN ShelfTable AS shf ON shf.ShelfID = inv.ShelfID
WHERE shf.ShopID = @MyShopID
;

Here are a few take-away points to consider. Notice we handled the FROM clause first. You'll always want to do that.

You'll also want a "driving table" to start with, in this case, [InventoryTable]. The other tables in your join add extra information and provide you a means to filter, but don't otherwise interfere with your summing up. More complex queries don't offer such an obvious luxury, but we're not getting too fancy here.

You'll also note, just briefly, that because [ShelfID] is a primary key in [ShelfTable], those [ShelfID]'s are unique values in [ShelfTable], and so each "inventory" thing belongs to a single "shelf." So the join won't cause us to double-count values. That's a good thing to remember when you're not dealing with primary and foreign keys, like we're doing here.

Hope that helps. And I hope I didn't come across as too pedantic.