This is my ugly solution: SELECT MAX(IF(articles_attribs.attrib_key='content', articles_attribs. attrib_value, '')) AS content, MAX(IF(articles_attribs.attrib_key='description', articles_attribs. attrib_value, '')) AS description, MAX(IF(articles_attribs.attrib_key='keyword', articles_attribs. attrib_value, '')) AS keyword, MAX(IF(articles_attribs.attrib_key='title', articles_attribs. attrib_value, '')) AS title, FROM articles LEFT OUTER JOIN articles_attribs ON (articles_attribs.article_id=articles.id) WHERE articles.id=1
But I do not really understand why this would be "normalized", it is a ugly and not so dynamic solution. /Peter -----Original Message----- From: Michael Caplan [mailto:[EMAIL PROTECTED] Sent: Thursday, July 20, 2006 12:54 AM To: mysql@lists.mysql.com Subject: Normailizing SQL Result Set Hi there, I am trying to work through a DB design that is giving me some trouble with the result sets. The situation is this: I have a table called "articles" and a related table call "article_attributes" Within the table "articles" I am maintaining basic info about an article, such as article id, active, etc. However, I am not maintaining any lanuage specific data about the article in that table (title, description, body, etc). All that info is stored in "articles_attributes". The goal of storing all language specific info about an article in a seperate table is two fold: [CODE][/CODE] 1) I wish to maintain multiple language versions of an article without prior knowedge to the languages I have to handle, 2) I also wish to be able to easly add arbitrary new article attributes (eg: footnotes) without needing the modify the db tables. This is what I came up with for the structure: CREATE TABLE `articles` ( `id` int(10) unsigned NOT NULL default '0', `active` tinyint(1) NOT NULL default '0', PRIMARY KEY (`id`), KEY `active_idx_idx` (`active`) ) CREATE TABLE `articles_attribs` ( `article_id` int(10) unsigned NOT NULL default '0', `locale` varchar(5) NOT NULL default ' ', `attrib_key` varchar(255) NOT NULL default ' ', `attrib_value` longtext NOT NULL, PRIMARY KEY (`article_id`,`locale`,`attrib_key`), KEY `values_idx_idx` (`attrib_value`(767)) ) This works okay, however, when querying the database for one record, because of the join between articles => articles_attributes, I don't get one result set, but rather 1 X the number of attributes recorded for the article: +----+--------+------------+--------+-------------+------------------------- --------+ | id | active | article_id | locale | attrib_key | attrib_value | +----+--------+------------+--------+-------------+------------------------- --------+ | 1 | 1 | 1 | en_CA | content | <h1>Some Content</h1> | | 1 | 1 | 1 | en_CA | description | This is the article description | | 1 | 1 | 1 | en_CA | keyword | These are, article, keywords | | 1 | 1 | 1 | en_CA | title | Test article | +----+--------+------------+--------+-------------+------------------------- --------+ What I am struggling with is an elegant way I can normalize the result set. I want to end up with a result set that looks like this: +----+--------+------------+--------+-----------------------+--------------- ------------------+------------------------------+--------------+ | id | active | article_id | locale | content | description | keyword | title | +----+--------+------------+--------+-----------------------+--------------- ------------------+------------------------------+--------------+ | 1 | 1 | 1 | en_CA | <h1>Some Content</h1> | This is the article description | These are, article, keywords | Test article | +----+--------+------------+--------+-----------------------+--------------- ------------------+------------------------------+--------------+ I can programmatically go through the result set and flatten it so that all attrib_key values => corresponding attrib_value, but this is less than ideal. I'm wondering if any of you have ideas how I can acheive the desired result with some creative SQL? Thanks, Michael -- Michael Caplan - Zend Certified PHP Engineer Programming Manager Apison Communications Suite 110, 151 Provost Street New Glasgow, NS, Canada B2H 2P6 Phone: (902) 695-3375 Toll Free: (800) 845-6998 Fax: (902) 695-7777 email: [EMAIL PROTECTED] URL: http://www.apison.com Specializing in web development, graphic design and Internet marketing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
