Topic: SQL Help

[ober]

OK, so imagine the following scenario: You have objects and those objects have many attributes and you store each one of those attributes as a row in a table.  But not every object has every attribute.  Now let's say you want to display each object and a true/false table of which attributes an object has and doesn't have.  What is the best way to grab that data with SQL to reduce the post-processing?

For those visual folks, here is a table:

Object | Attribute
1            A
1            B
1            C
2            B
2            C
3            B

I want a table like the following:

Object  | Attribute A | Attribute B | Attribute C
1                 X                    X                   X
2                 0                    X                   X
3                 0                    X                   0

Thoughts?

[Mike]

Do you have a list of possible attributes in some other table?

Edit: Also how many attributes?

[Mike]

Not seeing a full solution yet but do take a look at: http://dev.mysql.com/doc/refman/5.1/en/exists-and-not-exists-subqueries.html

You could do a subquery to see if that attribute exists for each object.  Problem with that is if you add more attributes later it'd require redoing the query.

[ober]

Well, I found this which might just do the trick:

Code: [Select]

SELECT object,
MAX( IF(datatype='VV','X','') ) AS 'VV',
MAX( IF(datatype='SC','X','') ) AS 'SC',
MAX( IF(datatype='SCH','X','') ) AS 'SCH'
FROM attributes
WHERE datatype != 'sy' AND datatype != 'st' AND datatype != 'ry'
GROUP BY object
I generalized the query but you get the idea... now I just have to add as many MAX/IF statements as there are attributes (there are only 7).

[Mike]

Interesting approach.  Thanks for sharing.

[webwhy]

just curious...why was it modeled this way?  are end users adding attributes?  seems like a candidate for denormalization to me...

[ober]

I don't see anything wrong with the model at all.  The only other option is to store the data like this:

symbol | attribute1 | attribute2 | etc.

That would end up with a bunch of nulls and no added benefit.  Using my approach provides the best storage and allows me to have other attributes for the objects that aren't tied into this report (which also exists).

EDIT: And did you really suggest denormalizing something?  HEATHEN!

[webwhy]

i'm not saying there's anything wrong with it.  i don't know the problem.

the added benefit would be query performance for reads, inserts, updates and deletes and code simplicity, but obviously the choice of design is based on other factors as well.  if the set of possible attributes is fairly fixed, i'm going to look at the denormalized design first 

[ober]

Possible attributes will grow over time, that's for sure.

And I don't do denormalized.  Ever. 

[Perspective]

Maybe I missed something, but don't you just want to do an outer join? http://en.wikipedia.org/wiki/Join_(SQL)#Outer_joins

You'll get null values in the place of missing attributes.

[hans]

Looks like you might want to look at something like MongoDB for this sort of thing. Able to store different attribute sets for the same (similar) domain model.

We did a "custom fields" table in a previous project and it ended up causing performance problems that we had to work around and come up with really creative SQL.

The NoSQL sort of stuff gets tricky when you try to display the various stuff that may or may not be there, but might be worth a look.

[ober]

Perspective, I'm not really doing a join here.  Everything is in one table, I just need to rearrange it.

And tgm, I don't really need to go that far.  The SQL above did the trick perfectly.

[Perspective]

oh, i see. I got the impression that each attribute had it's own objectid -> attr table.

[ober]

Well, I found this which might just do the trick:

Code: [Select]

SELECT object,
MAX( IF(datatype='VV','X','') ) AS 'VV',
MAX( IF(datatype='SC','X','') ) AS 'SC',
MAX( IF(datatype='SCH','X','') ) AS 'SCH'
FROM attributes
WHERE datatype != 'sy' AND datatype != 'st' AND datatype != 'ry'
GROUP BY object
I generalized the query but you get the idea... now I just have to add as many MAX/IF statements as there are attributes (there are only 7).

So... the requirements have changed.  Now not only can it be a simple X output, but if a secondary field is .. say 999 or whatever, I have to output the value from another field.

I looked at using CASE in the select statement and that seems to work but only returns values for a single column when values exist for multiple columns.  Unfortunately I don't understand what the MAX above even does and in my basic testing, I can't make it do what I want.

This is what I have, but like I said, it only returns values for one column even if multiple columns have values:

Code: [Select]

$query = "SELECT s.symbol, sn.symbolName";

for($i=0;$i<count($keys);$i++)
{
    $query .= ", CASE
        WHEN datatype='".$keys[$i]."' AND datainterval = -1 THEN 'X'
        WHEN datatype='".$keys[$i]."' AND datainterval = 999 THEN txtvalue
        ELSE ''
        END AS ".$keys[$i]."";
}
$query .= " FROM securities AS s JOIN security_names AS sn ON s.symbol = sn.symbol
WHERE datatype != 'sy' AND datatype != 'st' AND datatype != 'ry'
GROUP BY symbol
ORDER BY symbol";