Solved

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

Posted on 2014-09-11
13
356 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 82

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
 
LVL 82

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now