SQL: Creating new table from data of other tables

Question

Hi, I'm very new to SQL and I hope someone can help me with some SQL syntax. I have a database with these tables and fields,

• DATA: data_id, person_id, attribute_id, date, value
• PERSONS: person_id, parent_id, name
• ATTRIBUTES: attribute_id, attribute_type

attribute_type can be "Height" or "Weight"

Question 1

Give a person's "Name", I would like to return a table of "Weight" measurements for each children. Ie: if John has 3 children names Alice, Bob and Carol, then I want a table like this

| date | Alice | Bob | Carol |

I know how to get a long list of children's weights like this:

select d.date, 
       d.value 
  from data d, 
       persons child, 
       persons parent, 
       attributes a 
 where parent.name='John' 
   and child.parent_id = parent.person_id 
   and d.attribute_id = a.attribute_id 
   and a.attribute_type = "Weight';

but I don't know how to create a new table that looks like:

| date | Child 1 name | Child 2 name | ... | Child N name |

Question 2

Also, I would like to select the attributes to be between a certain range.

Question 3

What happens if the dates are not consistent across the children? For example, suppose Alice is 3 years older than Bob, then there's no data for Bob during the first 3 years of Alice's life. How does the database handle this if we request all the data?

Thanks a lot!

Answer 1

when you do a select, you need to specify the exact columns you want. In other words you can't return the Nth child's name. Ie this isn't possible:

1/2/2010 | Child_1_name | Child_2_name | Child_3_name
1/3/2010 | Child_1_name 
1/4/2010 | Child_1_name | Child_2_name 

Each record needs to have the same amount of columns. So you might be able to make a select that does this:

1/2/2010 | Child_1_name
1/2/2010 | Child_2_name
1/2/2010 | Child_3_name
1/3/2010 | Child_1_name 
1/4/2010 | Child_1_name
1/4/2010 | Child_2_name

And then in a report remap it to how you want it displayed

Answer 2

1) It might not be so easy. MS SQL Server can PIVOT a table on an axis, but dumping the resultset to an array and sorting there (assuming this is tied to some sort of program) might be the simpler way right now if you're new to SQL.

If you can manage to do it in SQL it still won't be enough info to create a new table, just return the data you'd use to fill it in, so some sort of external manipulation will probably be required. But you can probably just use INSERT INTO [new table] SELECT [...] to fill that new table from your select query, at least.

2) You can join on attributes for each unique attribute:

SELECT [...] FROM data AS d
JOIN persons AS p ON d.person_id = p.person_id
JOIN attributes AS weight ON p.attribute_id = weight.attribute_id
HAVING weight.attribute_type = 'Weight'
JOIN attributes AS height ON p.attribute_id = height.attribute_id
HAVING height.attribute_type = 'Height'
[...]

(The way you're joining in the original query is just shorthand for [INNER] JOIN .. ON, same thing except you'll need the HAVING clause in there)

3) It depends on the type of JOIN you use to match parent/child relationships, and any dates you're filtering on in the WHERE, if I'm reading that right (entirely possible I'm not). I'm not sure quite what you're looking for, or what kind of database you're using, so no good answer. If you're new enough to SQL that you don't know the different kinds of JOINs and what they can do, it's very worthwhile to learn them - they put the R in RDBMS.