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


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


This is done with a JOIN.

NOTE: I'll assume your table are named product and unit; please replace those with the actual names if you try to use any of this.

If every row in product has a valid weight or dimension unit of measure ID- that is to say, if none of the IDs is NULL, and all ID values actually exist in unit - then you can do this with an INNER JOIN.

If some rows in product have a NULL value in that field, or have a value that doesn't match the actual ids in unit, and you'd still want those product rows to show up in your results, then you need to use an OUTER JOIN.

I'll construct a demonstration query using a LEFT OUTER JOIN for both IDs. IF you have a row where the actual unit row can't be found, then that unit name will come out as NULL:

      ,w_uom.[unit name] as [wt unit]
      ,d_uom.[unit name] as [dim unit]
  FROM product p
         LEFT OUTER JOIN `unit` w_uom ON (p.[weight unit of measure id] = w_uom.id)
         LEFT OUTER JOIN `unit` d_uom ON (p.[dimensions unit of measure id] = d_uom.id)

When joining to the same table twice, you must use a table alias so you can tell which joined table you're referring to.

| improve this answer | |

Looks like you're selecting the Unit of Measure (UoM) id column instead of the value column.

So if your current query looks something like:

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

You'll need to make sure you replace the [fimensions unit of measure id] column with [dimensions unit of measure VALUE] column

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.