Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

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

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
DrDamnit
Asked:
DrDamnit
  • 5
  • 3
  • 3
  • +1
2 Solutions
 
Dave BaldwinFixer of ProblemsCommented:
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
 
DrDamnitAuthor Commented:
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
 
Dan CraciunIT ConsultantCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Dave BaldwinFixer of ProblemsCommented:
Maybe you could give an example of the results you want and where they are coming from.
0
 
DrDamnitAuthor Commented:
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
 
Dan CraciunIT ConsultantCommented:
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
 
DrDamnitAuthor Commented:
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
 
Dan CraciunIT ConsultantCommented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
DrDamnitAuthor Commented:
@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
 
PortletPaulCommented:
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
 
DrDamnitAuthor Commented:
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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now