hi, I have "standard" organizations table with org_id, name, address, city,... columns.
CREATE TABLE `organization` ( `organization_id` int(8) unsigned NOT NULL default '0', `address_id` int(8) unsigned default NULL, `full_name` varchar(255) default NULL, `phone` varchar(255) NOT NULL default '', `fax` varchar(10) default NULL, `parent_org_id` int(8) default NULL, `website` varchar(45) default NULL, `country` varchar(45) default NULL, PRIMARY KEY (`organization_id`) ) ENGINE=MyISAM +-----------------+------------+-------------+------------+------------+----------------------------+ | organization_id | address_id | full_name | phone | fax | website | +-----------------+------------+-------------+------------+------------+----------------------------+ | 8200 | 1 | 1520 | 2122457777 | 2122457730 | http://www.abcinc.com | +-----------------+------------+-------------+------------+------------+----------------------------+ I have also custom_fields table CREATE TABLE `custom_fields` ( `field_id` int(4) NOT NULL, `field_display` varchar(100) character set latin1 NOT NULL, `field_type` enum('text','date') character set latin1 NOT NULL default 'text', `field_order` int(3) unsigned default NULL, `choices` text character set latin1, PRIMARY KEY (`field_id`) ) ENGINE=MyISAM *************************** 1. row *************************** field_id: 12 field_display: Start Date field_type: date field_order: 2 choices: *************************** 2. row *************************** field_id: 13 field_display: Cancel Date field_type: date field_order: 4 choices: *************************** 3. row *************************** field_id: 14 field_display: Membership Type field_type: text field_order: 6 choices: Large Member,Small Member,Associate Member,Individual Member *************************** 4. row *************************** field_id: 15 field_display: Referred By field_type: text field_order: 8 choices: and custom field values table CREATE TABLE `custom_field_values` ( `organization_id` int(8) NOT NULL, `field_id` int(4) NOT NULL, `cust_field_value` varchar(255) default NULL, PRIMARY KEY (`organization_id`,`field_id`) ) ENGINE=MyISAM mysql> select organization_id, field_id, cust_field_value from dir_custom_field_values where instance_id=12 and organization_id=8200; +-----------------+----------+------------------+ | organization_id | field_id | cust_field_value | +-----------------+----------+------------------+ | 8200 | 12 | 2005-04-01 | | 8200 | 14 | Small Member | | 8200 | 16 | 1-4 | | 8200 | 21 | Retail | +-----------------+----------+------------------+ I have to make a list (on screen, as html table) of organizations with custom fields as a part of the table, e.g. Org. ID | Org. Name | Org. Address | ... | cust_field_1 | cust_field_2 | cust_field_3 | ... but I can't make a query to put several records from custom_field_values for specific org_id in one row? Example: +-----------------+------------+-------------+------------+------------+----------------------------+------------+-------------+-----------------+ | organization_id | address_id | full_name | phone | fax | website | start date | cancel date | membership type | +-----------------+------------+-------------+------------+------------+----------------------------+------------+-------------+-----------------+ | 8200 | 1 | 1520 | 2122457777 | 2122457730 | http://www.abcinc.com | 2005-04-01 | 2006-01-01 | Smal Member | +-----------------+------------+-------------+------------+------------+----------------------------+------------+-------------+-----------------+ thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
