I am using mySQL and would like results returned in a certain way, but i can't figure it out besides doing a bunch of server side processing (which seems like what I may have to end up doing).

Basically i have 2 tables set up like this:
-------------------------------------

data_set
--------
id
set_name
timestamp

data
-----
id
data_set_id
data_type
data

basically a data set is any grouping of data. it could be for example (name, age, sex). so each time a data set is submitted, we get something like this inserted, with the actual data different:

data_set (1,'my_set', '#timestamp#')

and then 3 records would be inserted into data

(1, 1, text, 'josh')
(2, 1, int, '12')
(3, 1, text, 'yes')

So lets say I select some data_sets:

SELECT * FROM data_set

1, my_set, *timestamp*
2, other_set, *timestamp*

now i want to get the actual data that was submitted (there will be 3 rows for each set because there were 3 data itmes associated w/each set)

SELECT data FROM data WHERE ID in(1,2) ORDER BY my_set

returns something like this

josh
15
yes
samantha
27
allthetime


the problem is that I have 3 rows of results for each actual record, because i just throw one record for every data item because the data set is dynamic. what i would like to return is the records associated w/the same data_set in one row... just the data field is all i need, so it would be like

josh, 15, yes
samantha, 27, allthetime

basically combining all rows with the same parent into one record with a column for what used to be a row.

can SQL do this???? or do I need to dynamically build a query? i am thinking perhaps some type of nested queries? but i'm not sure.

that was extremely confusing i appologize.

[Edited on September 12, 2005 at 4:16 PM. Reason : .]

9/12/2005 4:14:55 PM

LEFT JOIN

9/12/2005 4:46:12 PM

SELECT t1.id, t1.set_name, t1.timestamp, t2.id, t2.data_set_id, t2.data_type, t2.data
FROM data_set AS t1, data AS t2
WHERE t1.id>0
ORDER BY t1.set_name ASC, t2.id ASC


that should return:

1,josh data,12june2004,1,1,text,josh
1,josh data,12june2004,1,2,int,12
1,josh data,12june2004,1,3,text,yes
2,samantha data,12june2004,1,4,text,samantha
2,samantha data,12june2004,1,5,int,27
2,samantha data,12june2004,1,6,text,all the time

of course, if you only wanted the data out of those, make the SELECT portion of the statement "t2.data" only.

[Edited on September 12, 2005 at 4:53 PM. Reason : .]

9/12/2005 4:49:00 PM

well see, the problem is, I know how to do it that way, I was hoping there was some type of command that would do that automatically based on an ID grouping, because the data set will be dynamic, so that query would need to be dynamically generated each time to work... i was hoping there was a way to have it auto do that... i'm guessing no and i have to create that query every time.

9/12/2005 5:07:04 PM

for instance one data set might be

name
sex
age

and another (with data stored in same DB) might be

name
race
income
children
weight

but i was hoping one query could figure that out and have the same effect in both situations.

9/12/2005 5:08:36 PM

seems like your question centers around MySQL being able to resolve data as field names

I have never seen this (but I'd like to). Along the same lines I don't think you can store SQL commands in MySQL and have it execute them inline.

9/12/2005 7:04:58 PM

I don't believe you have a choice other than dynamically generating the query strings.

Even the oracle web site says you have to do this, so I assume it can't be done with some DB feature (because oracle would have the feature).

http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/files/advanced/DynamicSqlSample/Readme.html

9/12/2005 7:49:38 PM

thanks for the info. i will just generate the query myself. I just didn't want to do that if there was a 100x more efficient way to do this with a query that I was not aware of.

9/13/2005 11:20:46 AM

I'm not sure about MySQL, as i've never used it. But MSSQL and ACCESS both have tools that let you link tables then create the queries using their toolset. It makes the best queries each time for situations like that.

9/13/2005 11:45:19 AM

this is a design problem more than an sql problem

i would suggest making data_type store the name of the data, like age, sex, name, rather than the variable type. basically all the variables will be text since data is going to have to be a varchar to store all these different types and sizes of data

then, when you use my statement that includes data_type along with data, you can parse out what the data actually refers to using php and have your script act accordingly.

basically, yes. there will need to be server side processing of the data. mysql just stores data the way you tell it to, and returns it the way you request it. mysql doesn't make decisions about how to store or return the data based on what the data is. that's what php is for. php gets the data then processes it and makes decisions based on that data the way you tell it to

^ joins are basically links, except that you can't physically drag a link from one column to another like you can in access.

[Edited on September 13, 2005 at 12:19 PM. Reason : .]

9/13/2005 12:16:53 PM

scrager, i have that information available, but i have normalized it into another table. i was just trying to get my query to return data in a certain way which would make php's job easier... but it can't be done so php will just have to suffer through it

9/13/2005 12:37:55 PM

if that data is in another table, can't you join with that as well to get what you want?

you can join more than one table, or the same table more than once to get what you want.

9/13/2005 1:11:14 PM