There are three tables: This stores just a cosmetic name and a user_id which is used to find all the account holders campaigns... CREATE TABLE `campaigns` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL default '0', `campaign_name` varchar(24) NOT NULL default '', `updated` timestamp(14) NOT NULL, `added` timestamp(14) NOT NULL default '00000000000000', PRIMARY KEY (`id`) ) TYPE=MyISAM AUTO_INCREMENT=4 ;
SELECT campaign_name from campaigns where user_id = 123; would show me all the campaigns for a particular user. I have another table, addresses, which stores unique to a account holder, certain email addresses: CREATE TABLE `addresses` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL default '0', `first_name` varchar(48) NOT NULL default '', `last_name` varchar(48) NOT NULL default '', `email_address` varchar(48) NOT NULL default '', `updated` timestamp(14) NOT NULL, `added` timestamp(14) NOT NULL default '00000000000000', PRIMARY KEY (`id`), UNIQUE KEY `emailuid` (`email_address`,`user_id`), KEY `email_address` (`email_address`) ) TYPE=InnoDB PACK_KEYS=0 COMMENT='das' AUTO_INCREMENT=351 ; The above email addresses can exist in more than one campaign, so I use a 3rd table to make that relationship: CREATE TABLE `addresses_incampaign` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL default '0', `email_address` varchar(48) NOT NULL default '0', `campaign_id` int(11) NOT NULL default '0', `updated` timestamp(14) NOT NULL, `added` timestamp(14) NOT NULL default '00000000000000', PRIMARY KEY (`id`), UNIQUE KEY `emailaddresscampaign` (`email_address`,`campaign_id`) ) TYPE=InnoDB PACK_KEYS=0 COMMENT='das' AUTO_INCREMENT=246 ; >From all this, I most often will need to find all email addreses owned by a user_id in a particular group, I am using the addresses_incampaign. email_address and addresses.email_address to link these together. I would have liked to use a auto_inc as the key to link them together, but for reasons that are too long to explain, that is not how the data is inserted. Is this a big deal? Or does mysql really not matter that a int is not in my field where I will be linking one record to another? Should I create any other indexes that I don't have here? I have made 2 joins, they both seem to get me the same result, I am not sure if either is optimum, can someone suggest some stuff to me: select a.first_name, a.last_name, a.email_address, c.campaign_name, i.campaign_id from addresses as a inner join addresses_incampaign as i on (a.user_id = i.user_id AND a.email_address = i.email_address) inner join campaigns as c on (i.campaign_id = c.id) WHERE i.campaign_id = '1' AND a.user_id = '1' AND i.user_id = '1' select a.first_name, a.last_name, a.email_address, c.campaign_name, i.campaign_id from addresses as a inner join addresses_incampaign as i on (a.email_address = i.email_address) inner join campaigns as c on (i.campaign_id = c.id) WHERE i.campaign_id = '1' AND a.user_id = '1' AND i.user_id = '1' Thanks -- ------------------------------------------------------------- Scott Haneda Tel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED] Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
