DVD Talk
moderately advanced SQL question [Archive] - DVD Talk Forum
 
Best Sellers
1.
2.
3.
4.
5.
6.
7.
8.
The Longest Day
Buy: $54.99 $24.99
9.
10.
DVD Blowouts
1.
2.
3.
4.
5.
6.
7.
Alien [Blu-ray]
Buy: $19.99 $9.99
8.
9.
10.

PDA
DVD Reviews

View Full Version : moderately advanced SQL question


moorehed
09-12-05, 04:20 PM
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.

X
09-12-05, 04:41 PM
You could probably do it with a union statement, selecting each record like:

select [desired fieldname] from [tablename] where ID = 1 and timestamp = [whatever]
union select [desired fieldname] from [tablename] where ID = 2 and timestamp = [whatever]
union select [desired fieldname] from [tablename] where ID = 2 and timestamp = [whatever]

moorehed
09-12-05, 05:01 PM
yeah i know i could do it like that... but the fields are dynamic, so i would have to build a new sql statement everytime... i was hoping there was some SQL keyword where i could have a row hold multiple rows basically.......

X
09-12-05, 05:04 PM
I guess I don't understand. What fields are dynamic?

Groucho
09-12-05, 05:06 PM
What exactly are you trying to do? This seems inefficient to me.

X
09-12-05, 05:08 PM
Yeah, I don't understand why he's creating three records. But if three or fewer identifiable records are always being created it shouldn't be difficult to union them.

moorehed
09-12-05, 05:12 PM
okay here is what is going on.

i have dynamic forms so that any user can make a form on his website with any fields he wants. when someone submits a form, it needs to store all the info in a database. because the form is dynamic, i can't have a database set up like this:

id, name, age, sex

because it needs to work no matter what form fields the user ads. so i just have a general table that holds data item (1 record = 1 data item). so it throws 3 separate data items in, and then gives them a parent and they all share the parents foreign key.

when i query them though, i would like it to actually appear as 1 record for one submission... but i can't do it. lol

moorehed
09-12-05, 05:13 PM
if you can think of a better way to store information in a database that is submitted via form, but the form fields could be anything, let me know. i debated putting it in w/xml or something but that sounded like a PITA

sfsdfd
09-12-05, 05:55 PM
So here's the problem: You want the returned record to have a variable size, depending (solely) on how many records exist for that data set ID. In other words, you want each record to have count(data_set_id) fields.

As far as I know, that's really not possible. Not in a clean way for a potentially unlimited width of data records. Consider what happens if one record has more fields than another - do you really want a jagged data set? (record 1 has 3 fields, record 2 has 6 fields, record 3 has 5 fields, etc.)

What you <i>can</i> do is assume a fixed maximum number of fields - say, 10. Just enumerate the fields from 1 to 10, and then assemble the result set with that number of attributes. Of course, setting this field higher imposes incrementally more work to generate the result query, so performance will drop - you'll have to tweak it.

- David Stein

Groucho
09-12-05, 06:00 PM
It almost sounds like you're building a web-based GUI front-end for MySQL.

But if the user can customize the kinds of forms they make, why not use the "CREATE TABLE" functionality of MySQL to create the exact table they need for their data? Does that make sense?

Mordred
09-12-05, 06:49 PM
It almost sounds like you're building a web-based GUI front-end for MySQL.

But if the user can customize the kinds of forms they make, why not use the "CREATE TABLE" functionality of MySQL to create the exact table they need for their data? Does that make sense?That's a decent option and one I would use if I had to do it all in SQL. Of course doing it all in SQL would be silly... I'd just wrap the MySQL in some PHP code to format the data exactly how I want it which wouldn't take more than a few minutes time. I'm assuming moorehed has some reason for not just wrapping the SQL though, although I can't figure out what it would be.

moorehed
09-13-05, 11:50 AM
So here's the problem: You want the returned record to have a variable size, depending (solely) on how many records exist for that data set ID. In other words, you want each record to have count(data_set_id) fields.

As far as I know, that's really not possible. Not in a clean way for a potentially unlimited width of data records. Consider what happens if one record has more fields than another - do you really want a jagged data set? (record 1 has 3 fields, record 2 has 6 fields, record 3 has 5 fields, etc.)

What you <i>can</i> do is assume a fixed maximum number of fields - say, 10. Just enumerate the fields from 1 to 10, and then assemble the result set with that number of attributes. Of course, setting this field higher imposes incrementally more work to generate the result query, so performance will drop - you'll have to tweak it.

- David Stein

thanks for the info guys... looks like i am just going to have PHP sort out the results into an array and then deal with it there... i just didn't want to do it that way if there was a quick easy way to do it with sql... but there's not :D

Mordred
09-13-05, 01:47 PM
Your one issue is that you don't know how many fields there are for each data set. I would propose adding a num_fields field to data_set, otherwise you're going to have parsing problems.

Once that's done it's going to be really easy to do that in PHP.

Logically it'd look like:

SELECT data_set
open table
SELECT data WHERE data.id = data_set.id
While you have records
&nbsp;&nbsp;add row
&nbsp;&nbsp;for (i=0;i &lt; num_fields;i++)
&nbsp;&nbsp;&nbsp;&nbsp;add column with data.data
end while
close table