Hello,
I have three tables: CREATE TABLE event ( eid int(10) unsigned NOT NULL auto_increment, tid smallint(5) unsigned NOT NULL default '0', itimestamp int(10) unsigned NOT NULL default '0', etimestamp int(10) unsigned NOT NULL default '0', somethingelse tinyint(4) NOT NULL default '0', PRIMARY KEY (eid) ); CREATE TABLE line ( eid int(10) unsigned NOT NULL default '0', position tinyint(3) unsigned NOT NULL auto_increment, did int(11) NOT NULL default '0', PRIMARY KEY (eid,position) ); CREATE TABLE data ( did int(10) unsigned NOT NULL auto_increment, data varchar(255) binary NOT NULL default '0', PRIMARY KEY (did), UNIQUE KEY data (data) ); Basically I insert data into event and record the resulting eid from the auto increment. Then I insert my raw data into the data table, recording each did from the auto increment. Lastly, I insert multiple records into the line table, one for each of my raw data elements. I then am able to do: SELECT event.eid,event.tid,event.itimestamp,event.etimestamp,data.did,data.data FROM event LEFT JOIN line ON event.eid=line.eid LEFT JOIN data ON line.did=data.did WHERE event.eid='45' to get a single event. This all works fine, however this is the result set I get back: 45 48 1012359206 1012134660 1 Jan 45 48 1012359206 1012134660 2 27 45 48 1012359206 1012134660 61 07 45 48 1012359206 1012134660 39 31 45 48 1012359206 1012134660 5 00 45 48 1012359206 1012134660 6 ahost 45 48 1012359206 1012134660 69 14823 45 48 1012359206 1012134660 10 my.host.com My question is there any way to consolidate the rows, so that I avoid the redundant data? In other words: 45 48 1012359206 1012134660 1 Jan 2 27 61 07 39 31 5 00 6 ahost 69 14823 10 my.host.com I could do this in multiple queries, which works fine when there is only one event returned, but if I query on a different field (event.tid for example, which is a type of line) things get very messy. Currently I am handling this consolidation in PHP, but it's CPU and memory intensive and it just seems a waste to have so much of the same data returned. If anyone could give me some pointers on doing this, or, let me know if I'm missing something obvious or that there is a better way to possibly design my schema. Please feel free to ask me anything for clarification etc. Thank you Hans -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
