Hi Shawn,
I think I may have found a solution, but the query takes quite a while to run...here is what I have now paraphrased:
SELECT vd.id AS id, vd.session AS session, vl.value AS content, vd2.varvalue AS browser, vl3.value AS bandwidth FROM vardata AS vd
LEFT OUTER JOIN vars AS vn ON ( vn.name = 'content' AND vn.id = vd.nameid ) LEFT OUTER JOIN valuelookup AS vl ON ( vl.id = vd.valueid )
LEFT OUTER JOIN data AS vd2 ON ( vd2.session = vd.session ) LEFT OUTER JOIN vars AS vn2 ON ( vn2.id = vd2.nameid )
/* and so forth for each variable... */ WHERE vd.campaignId = 17 AND vn.name = 'content' AND vn2.name = 'browser' AND vn3.name = 'bandwidth'
This would result in the following 'flattened' structure:
[id] [session] [content] [browser] [bandwidth] 22 55 intro MSIE 6.0 hi 23 55 form1 MSIE 6.0 hi 23 56 intro Firefox 1.0 lo
Only problem is that the status of the thread stays in 'statistics' forever... 10 mins last time I checked before cancelling it...
Seems like the time is increasing quite drastically as I pile on more variable comparisons... I am guessing because I am increasing that number of comparisons that must be done...
Thanks, Mathew
[EMAIL PROTECTED] wrote:
Mathew Ray <[EMAIL PROTECTED]> wrote on 03/24/2005 10:42:51 AM:
Been searching for a while and can't seem to come up with any good answers to this - I have a normalized structure of about 5 tables that I
need to denormalize into one big representation of the entire structure.
Anyone know of a good tool or resource to 'flatten' my tables easily?
I've got a query that removes all foreign key info and creates a column that holds the linked value, but my problem is that I have some columns that change with every row and others that should only be logged once per row (responses to polls for example).
Here is a simplified structure: [data] id (int) nameid (int) valueid (int)
[vars] id (int) name (varchar 255)
[valuelookup] id (int) value (varchar 255) varid (int)
Some variables, like 'browsertype', are consistent for every item, others like 'content' change for every row. The 'poll' variable contains
multiple choice responses, but shouldn't be double counted when the table is de-normalized. I looked at some open source E-T-L tools, but they look pretty complicated, and I feel there is a way of doing this with sql somehow. Any idea on how to approach this?
--
Thanks,
Mathew
Show us some concrete examples of how you want your denormalized data to look and I am sure we can help.
Shawn Green Database Administrator Unimin Corporation - Spruce Pine
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
