[Resolved] JOIN associated records into one record?

[sneakyimp]

i have a table like this in mySQL:

ID   NAME
1    Doctor
2    Lawyer
3    Prostitute

I have a table that associates other stuff to it like this:

ID  JOB_ID  RECORD_TYPE  VALUE
1   1       salary       200000
2   1       respect      high
3   1       sexiness     low
4   2       salary       150000
5   2       respect      medium
6   2       sexiness     none
7   3       salary       50000
8   3       respect      none
9   3       sexiness     high

The JOB_ID field in the second table connects to ID in the first table. If possible, i would like to JOIN the two tables and get a result that looks something like this:

ID  NAME        SALARY  RESPECT  SEXY
1   doctor      200000  high     low
2   lawyer      150000  medium   none
3   prostitute  50000   none     high

Is this possible? How about if I want to apply the query to other tables which might have different numbers of fields?

Any help would be much appreciated.

[nemonoman]

This is a standard SQL technique, and the form you want looks something like this:
SELECT main.id as 'ID' ,
main.name as 'NAME' ,

if( secondary.type='salary', value,'') as 'SALARY',
if( secondary.type='respect', value,'') as 'RESPECT',
if( secondary.type='sexiness', value,'') as 'SEXY'
FROM
main, secondary
WHERE
main.id=secondary.job_Id
GROUP BY main.id

[sneakyimp]

that seems so obvious in retrospect. i will try that. i had been working on a join approach that makes a really long query...like this:

SELECT
  c.id,
  c.name,
  cd0.value AS salary,
  cd1.value AS respect,
  cd2.value AS sexiness
FROM careers c
LEFT JOIN career_data cd0 ON c.id = i0.job_id
LEFT JOIN career_data cd1 ON c.id = i1.job_id
LEFT JOIN career_data cd2 ON c.id = i2.job_id
WHERE cd0.type = 'salary'
AND cd1.type = 'respect'
AND cd2.type = 'sexiness';

which do you think will be faster?

[sneakyimp]

hey nemonoman:

i tried your approach on my actual database...the job tables were just a simplified example of a slightly more complex problem. i tried taking the approach you suggested for my real database tables and the result seems close to what i want, but only one data field contains any value for each resulting record.

here's the actual query:

SELECT c.code, c.title, 
IF ( i.element_id =  '1.B.1.c', i.data_value,  '' ) AS  's0', 
IF ( i.element_id =  '1.B.1.f', i.data_value,  '' ) AS  's1', 
IF ( i.element_id =  '1.B.1.e', i.data_value,  '' ) AS  's2', 
IF ( i.element_id =  '1.B.1.b', i.data_value,  '' ) AS  's3', 
IF ( i.element_id =  '1.B.1.a', i.data_value,  '' ) AS  's4', 
IF ( i.element_id =  '1.B.1.d', i.data_value,  '' ) AS  's5'
FROM careers c, interest i
WHERE c.code = i.code
GROUP  BY c._code

the complicated join query i suggested in my previous post correctly results in 6 scores for each record. this second approach only has a value for s4. you'll note that s4 comes from records with an element_id of 1.B.1.a which sould sort first.

I'd just go with my query if it weren't for the fact that your approach ran nearly 5 times faster. if i could get it working and producing the correct values, i'd much prefer your approach because i think it will be faster.

any tips would be much appreciated. thanks for your help so far.

[nemonoman]

Sorry, I think I've given you bad advice.
The approach I've suggested works when the columns have a group function, eg

SUM(if(date BETWEEN '2004-01-01' AND '2004-12-31, amount, 0),
SUM(if(date BETWEEN '2005-01-01' AND '2005-12-31, amount, 0)

etc., which in the example above would show summary year on year results.

I think you're stuck doing a multi-aliased join.

Your join should be fast, however...not appreciably different from the code I gave you.

I would index interest.code separately.

[sneakyimp]

hey no problem. thanks for the help 😉

i have my big ugly join working. i tried the sum approach too and the join seems faster--i think.

[sneakyimp]

arg!

i'm now working on this problem for a separate query...with 35 fields! mySQL will only let you join 31 tables.

i guess i'll have to use your approach for this query after all.

[bleurose]

Hi,

I saw the posts on using the GROUP by to create a simple cross-tab.

If you have a fixed number of columns (regardless of the number, and I believe you said you had 32), use the GROUP BY and use the MAX function (

SELECT main.name
MAX(IF(secondary.type = "sexiness", secondary.value, '')),
MAX(IF(secondary.type = "salary", secondary.value, '')),
...
FROM main,secondary
where main.id = secondary.job_id
GROUP BY main.id

The MAX function should be faster and will give you appropriate answers IF there is only one value for each secondary.id for each job_id. If there are multiple values, you will always get the largest value.

There are some special SQL extensions in various SQLs which do cross correlations like this without having to know the predetermined values.

Good luck!

Jon

[nemonoman]

Yes, assuming that the value is MAX able OR MIN able, your comments are right.

But the more I think about this sneakyimp, the more I think the DB design is screwy. These look more and more like Entity Attributes, not 1 to many relationships.

I think maybe you need to add 32 columns to your careers column. If you are worried you could use a tinyint to point to the a table values if you didn't want to include actual words like, 'sexy', etc.

[bleurose]

Actually, I think the design is good, its just that SQL is NOT intended to be a reporting language, but rather a data query language (but we all misuse it as a quasi-reporting language all the time).

Your proposal (having 32 columns for the different attributes is highly denormalized. It would, for instance, require adding a new column to the table each time a new possible attribute is needed. This is NOT good design.

The design that was proposed originally is actually quite sound. Although the description suggests a fixed number of attributes (3 per job type), in fact, it permits an unlimited number (by simply adding more attribute types in the secondary table) and it also permits a variable number of those attributes per job type (even though the example shows a fixed number of 3). This eliminates unnecessary data in the attribute (secondary table). Although the example doesn't illustrate this, if there are 30+ possibly attributes, and most job types only have a few of these, your approach would create data space (or at least null entries) for most of them for each job type. Not a good design, IMHO.

The problem is that SQL was intended to be used in conjunction with a programming or scripting language (as we often do), not purely by itself. In that context, there is no need to try to do what the original poster asked, i.e., create a "cross-tab" in SQL.

Instead, you would use the query language to create a join of the two tables (a simple join) across the ID/JOB_ID columns which would create each job with all of its attributes as rows (regardless of the number). Then you would use a programming lanugague, scripting language or reporting language to turn that query into a viewable x-tab display or table.

Because most SQL dialects have taken on all sorts of additions, we start to think of SQL as a reporting tool, which it wasn't intended to be.

Jon

[nemonoman]

Actually, adding attribute values to an entity is entirely within the normal form. PROVIDED that those elements are in fact attributes of the entity, and not entities on their own. I am inclined to believe that this is the case.

There are well established normalizing criteria for collapsing entities. This is done when (as appears to be the case here) a THEORETICAL entity (interest data) has PRACTICAL APPLICATION only as attributes of single entity.

Especially so, if each entity attribute is NOT NULL. Since sneakyimp is using an INNER, not a LEFT JOIN, this would also appear to the case.

All good database design, even highly normalized designs, must reflect practical considerations.

Your proposed solution proves that the data elements can be collapsed. Sneakyimp's problem (exceeding the number of tables that can be joined) shows the practical consideration.

Collapsing the entities is a normal response under the current circumstances.

PS. Having a lot of columns in a table is NOT a guarantee that the table is denormalized.

If the db designer is dullard, AND there are a lot of columns in a table, one can suppose that denormalization is afoot.

If the db designer is smart and there are a lot of columns, there's reason to explore, not to instantly condemn the design.

[sneakyimp]

there is actually a many-to-one relation between the two tables. the 1st table is a table of careers. the 2nd table contains scores. Generally, speaking a 'set' of scores has 35 score values. There are multiple sets of scores associated with each career. I'm not sure what the different sets of scores mean...I'm just dealing with one particular score set for now.

Additionally, each score has other values connected to it such as standard error, sample size, etc.

I didn't design the 2 tables, for what it's worth. I am considering deriving a table from the scores table that simply has all 35 scores in one record.

What does normalized/denormalized mean in this context?