CODEWITHSUNDEEP
t-sqljoininner-join
ScottC
ScottC

Reputation: 462

Selecting/Inner Joining multiple values from one table into a single row

I've been searching google all afternoon I'm not even sure how I would word the question I have so that google or bing might understand it. So I have 2 tables(the tables and the database setup were not my doing) one is the details of a product 

name, value, weight, weight unit of measure id, length, width, height, dimensions unit of measure id, description

Then another table with the units of measure and their ID values

eg. 

id, unit name
1,     cm
2,   inches
3,     lbs
4,     kg
5,    feet

I need to select from the products table, but replace the unit of measure ID's with the actual unit of measure text from the other table.

the data looks like

sm widgets, $10, 20, 3, 10, 10, 10, 1, small widgets

I want the results to come out like 

sm widget, $10, 20, lbs, 10, 10, 10, cm, small widgets 
lg widget, $15, 50, kg, 10, 10, 10, inches, large widgets

thanks for any insight you guys can give me

Upvotes: 0

Views: 75

Answers (1)

Jesse
Jesse

Reputation: 873

I think you just need to join the tables and return the description:

select
    p.name, 
    p.value, 
    p.weight, 
    c.[unitname],
    p.length, 
    p.width, 
    p.height, 
    c2.[unitname] as DimensionUnitName, 
    p.[description]
from products p
inner join unitcodetable c
    on c.id = p.[weight unit of measure id]
inner join unitcodetable c2
    on c2.id = p.[dimensions unit of measure id]

Upvotes: 2

Related Questions