Solved

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

Posted on 2014-09-11
13
365 Views
Last Modified: 2014-09-12
I have a series of tables, that have been simplified into the scheme pictured below.
The basic tables
The objects table contains data about a given object, and has a lot of useful data in it. But, because we may want to track additional and varied data, we have the other three tables, which include optional data for that object.

So, let's assume the "object" is a shopping cart item: T-Shirt. The manufacturer and other data would be in the object table. But, because "T-Shirt" can have many designs, we would store the design, size, colors, etc... in the key / value tables, and then associate them to the "object" table using the data table.

So, here's the quandary.

I want to query some specific information about A particular T-shirt that has a particular size and design. In order to do that, I need to run three queries: two that get the key / value pairs for an object that has an object id = x where height is one key and width is another key. Then another query for the object that has an id of x. If I try to do this as a subquery, MySQL complains that the subquery returned multiple rows. I can fix the multiple row problem by supplying objects_id = 14 to the subquery, but that's stupid because I would return the width and height for object #14 for all objects.

I am sure this question is clear as mud, so if I have made it more complicated than it should be, please call me out on that.

Thanks in advance,
DrD.

So, in order to get the values I want
0
Comment
Question by:DrDamnit
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40317638
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.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 40317684
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.
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40317704
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?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40317707
Maybe you could give an example of the results you want and where they are coming from.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 40317785
OK. Let's simplify things a bit.

Let's just talk about two tables: object and meta-data:
Le Diagram is French for "The Diagram"
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:

Real data from dev
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?
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40317798
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"

Open in new window

HTH,
Dan
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 40317825
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:
Results I want (made in excel)
0
 
LVL 34

Assisted Solution

by:Dan Craciun
Dan Craciun earned 200 total points
ID: 40317832
OK, then how about something like this:
SELECT object_id, a.value AS width, b.value AS height, c.value AS unit FROM objects
LEFT JOIN meta_data AS a ON objects.objects_id = a.objects_objects_id AND key = "WIDTH" 
LEFT JOIN meta_data AS b ON objects.objects_id = b.objects_objects_id AND key = "HEIGHT" 
LEFT JOIN meta_data AS c ON objects.objects_id = c.objects_objects_id AND key = "UNIT" 

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40318421
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?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40318468
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
;

Open 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

Open in new window

0
 
LVL 32

Author Comment

by:DrDamnit
ID: 40318484
@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?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 300 total points
ID: 40318641
I have worked with a similar data structure in the past for content management and they are very flexible, but when you required typed (decimal, integer, date etc.) attributes they get ugly - which is why I asked if you also had information on the data type of each [key]

I'm not proposing a change of data structure I'm simply pointing out that one of the consequences of it will be complex SQL.
-------

Can I do a combination here?
Yes.

(purely an example) such as this:
SELECT
      *
FROM (

            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
      ) p
      LEFT JOIN x
                  ON p.objects_objects_id = x.objects_objects_id
;

Open in new window

0
 
LVL 32

Author Closing Comment

by:DrDamnit
ID: 40319717
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.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question