How do I get all this information in a single query result?

If it is a shopping cart, then I would expect to see all the info on a single product in one row in one table.  Products are not normally variable like you are showing.  A t-shirt with a particular design would normally have it's own product id and a t-shirt that is identical except for the design would have a different product id.  The shopping cart that I maintain is a single table with about 50 columns that cover all the specifics for each product.  This is separate from the inventory table by the way.

It's not a shopping cart. I was just using that as an example.

The reason we have this structure is that some objects have specialized data that only objects of that class have.

Sort of like class mechanic extends class people. The properties of the people class are included in the properties of the mechanic class, but not all the properties of the mechanic class are included in the people class.

IDGI. What's your input?
You have 2 values_value, 1 keys_key and 1 values_value or what?
And the end result is objects_name or objects_important_value?

Maybe you could give an example of the results you want and where they are coming from.

OK. Let's simplify things a bit.

Let's just talk about two tables: object and meta-data:

Here are our new givens:
meta-data contains data about an object. We use a separate table because  not all  objects require the same data. (Sort of like how meta data is used in Wordpress to augment posts and user accounts with custom fields.... well... almost exactly like that).

So, I have some group of objects that will have the following three key value pairs:
Width => some integer
Height => some integer
Units => [inches|feet|milimeters|miles]

Objects that do not have these three keys are not needed for the report I am trying to generate.

So, some example data (from the dev box) looks like this:


This shows the four columns:
1. meta-data ID 177-183
2. The key (many keys are shown, some blurred out to avoid confusion), but hte ones that I care about are width, height, and unit.
3. The values (this object has dimensions of 43.25" x 148.5").
4. the object id (in this case, 21).

I would like these three highlighted rows to become three columns in a query that is executed against the parent table: objects.

If I was doing this in PHP, I would build a loop.
<psuedoCode>
for($i = 0 ; $i < 201;$i++)
$query = "SELECT * FROM objects WHERE objects_id = $i"
//Save results into array1
$query = "SELECT * from meta-data WHERE objects_id = $i AND meta-data.key IN ('WIDTH','HEIGHT','UNITS');
//Loop through all results, and merge purge into a single table or array

//Spit out report.
</pseudoCode>

Does this help clarify?

Try this (untested):

SELECT value AS width FROM meta-data WHERE objects_objects_id = "21" AND key = "WIDTH"
UNION
SELECT value AS height FROM meta-data WHERE objects_objects_id = "21" AND key = "HEIGHT"
UNION
SELECT value AS unit FROM meta-data WHERE objects_objects_id = "21" AND key = "UNIT"

Select allOpen in new window

HTH,
Dan

This would work, EXCEPT I need to run this as a report for ALL OBJECTS, and therefore, I cannot specify the object_id...

I am expecting to get results like this:

yowsers, these obscure methods of data storage lead to no end of grief when you actually need to use them.

You are seeking to pivot your non-normalized data into a normalized table BUT, in addition
You also have all [value] values as varchar(45)

Are you expecting conversion of [value] into more logical data types as well?

e.g. width, height, length as decimals, if so do you have that meta data description of data type somewhere?

A classic pivot (before a "pivot feature" existed) uses case expressions and group by, like this.
It should bemore efficient than multiple left joins, and simpler IMO than the "PIVOT"

SELECT
      objects_objects_id
    , MAX(CASE
            WHEN [key] = 'WIDTH' THEN value END)  AS WIDTH
    , MAX(CASE
            WHEN [key] = 'HEIGHT' THEN value END) AS HEIGHT
    , MAX(CASE
            WHEN [key] = 'LENGTH' THEN value END) AS LENGTH
    , MAX(CASE
            WHEN [key] = 'UNIT' THEN value END)   AS UNIT
FROM [meta-data]
GROUP BY
      objects_objects_id
;

Select allOpen in new window

I should add that if you provided "sample data" and "expected result" from your original 4 tables then we can propose solutions. For example this SQLfiddle can be used to test the query above.

    CREATE TABLE [meta-data]
    	([data_id] int, [key] varchar(45), [value] varchar(45), [objects_objects_id] int)
    ;
    	
    INSERT INTO [meta-data]
    	([data_id], [key], [value], [objects_objects_id])
    VALUES
    	(179, 'WIDTH', '43.25', 21),
    	(180, 'HEIGHT', '148.5', 21),
    	(181, 'UNIT', 'inch', 21),
    	(182, 'EXACT', NULL, 21)
    ;

**Query 1**:

    SELECT
          objects_objects_id
        , MAX(CASE
                WHEN [key] = 'WIDTH' THEN value END)  AS WIDTH
        , MAX(CASE
                WHEN [key] = 'HEIGHT' THEN value END) AS HEIGHT
        , MAX(CASE
                WHEN [key] = 'LENGTH' THEN value END) AS LENGTH
        , MAX(CASE
                WHEN [key] = 'UNIT' THEN value END)   AS UNIT
    FROM [meta-data]
    GROUP BY
          objects_objects_id

**[Results][2]**:
    
    | OBJECTS_OBJECTS_ID | WIDTH | HEIGHT | LENGTH | UNIT |
    |--------------------|-------|--------|--------|------|
    |                 21 | 43.25 |  148.5 | (null) | inch |



  [1]: http://sqlfiddle.com/#!3/a51c3/2

Select allOpen in new window

@PortletPaul, Re: "Yowzers" comment:

I am very much open to learning alternative (more efficient forms of storage). How would you suggest?

I originally learned to do this type of data storage from osCommerce and its implementation of options for products in a shopping cart. I am always looking for better ways of doing things.

Re: Pivot vs Left Join:

I do need at least one left join, because the existing report query I am working with doesn't reference the meta-data table to generate the existing report. I am looking to add these values to the report.

Can I do a combination here? a LEFT JOIN first, then pivots after that for efficiency?

This was a great set of answers. The final query was based more on PortletPauls' posts and turned out to be significantly faster considering I have an incredible number of rows in the tables. Thanks to both of you. My main problem here was that I was making it too hard on myself, thinking the subquery would be very complicated because I couldn't get past the "PHP programmer's mindset" of a loop. Dan showed me the simplest solution, which was vital to me understanding the more efficient (final) solution from Paul.