Need help joining two tables by guid then adding values across columns by row

Question

I have two tables.

One table stores items and their cost and are stored uniquely with their own guid.

ItemCost Table

Item_ID     ItemCategory   Item    Cost

x-xx-x      Computer       Laptop  400.00

The other table stores a work space that is a collection of these items, best illustrated.

Work Space Table

WorkSpace_ID    Workspace_Name   Item_Category_1     Item_Category_2 and so on....

xx-xx-xx         Workspace A     xx-xx-xx          (the guid from the first table)

Now I need some way to join the cost from the ItemCost table to the guid's they represent in the Work Space table, then, add them up across the columns to give a total cost of the work space. Some Item_Category columns in the Work Space table will be null.

Fairly new at SQL, kind of learning by fire here. If there's a better way to structure this, I'm open for suggestion.

Thanks.

What are you using this software for? Inventory, orders, accounting? It make a differnce inteh design if you need to have the cost as of the time it is associated with the workspace or the current cost. — HLGEM, Mar 19 '13 at 19:06

score 2 · Accepted Answer · answered Mar 19 '13 at 19:08

Well part of your problem is the structure of the Workspace table. This table is not normalized.

In you current design you will need to unpivot or join on the workspace table multiple times to get the result.

Current Structure Query will be:

select *
from itemcost i
left join
(
  select workspace_id,
    worksapce_name,
    item_category_1 ItemCategory
  from workspace
  union all
  select workspace_id,
    worksapce_name,
    item_category_2
  from workspace
) w
  on i.Item_ID = w.ItemCategory

Typically you would have a Workspace table and a separate table to link the ItemCost to the Workspace called workspace_items:

create table workspace
(
  workspace_id, 
  Workspace_Name
);

create table workspace_items
(
  workspace_id, 
  Item_ID
);

This will make the joining on the table much easier. This structure allows you to have multiple item categories assigned to each workspace. If you alter your table structure, then your query will be:

select *
from itemcost i
left join workspace_items wo
  on i.item_id = wi.item_id
left join workspace w
  on wi.workspace_id = w.workspace_id

Thanks for the help, As you and Shlomo suggested, I just created another table. Made life a lot easier. Thanks again. — user2187945, Mar 20 '13 at 18:47
@user2187945 Happy to help, You will see that this structure will be much easier. :) — Taryn, Mar 20 '13 at 18:49

Shlomo · Answer 2 · 2013-03-19T19:16:54.233

0

You need to create another many to many table between WorkSpaces and Items (and remove the Item columns from the Workspace table):

WorkSpaceItems Table: WorkSpace_ID -- ItemID

Then it's easy to query all the WorkSpaceItems, join that to the Items table and sum that up.

Here's a link to schema and sample query: http://www.sqlfiddle.com/#!3/35336/7

edited Mar 19 '13 at 19:16

answered Mar 19 '13 at 19:06

Shlomo

14,102
3
28
43

Need help joining two tables by guid then adding values across columns by row

2 Answers2