I want to make a relational link from `data` to `shopping` so when I
insert a new record in `shopping`, I will see the contents of
`data`.`name` and `data`.`email` as drop-down menus in `shopping`.

This is InnoDB so I should be able to do this by SQL, right?

Thanks,
John


DROP TABLE IF EXISTS `data`;
CREATE TABLE `data` (
`id` int(5) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `data` VALUES(1, 'Allen, Carolyn', 'nobody@jtjohnston.net');
INSERT INTO `data` VALUES(2, 'Atwood, Margaret', 'someone@jtjohnston.net');

DROP TABLE IF EXISTS `shopping`;
CREATE TABLE `shopping` (
`id` int(5) NOT NULL auto_increment,
`name` varchar(100) NOT NULL,
`address` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

John Taylor-Johnston schrieb:
> I want to make a relational link from `data` to `shopping` so when I
> insert a new record in `shopping`, I will see the contents of
> `data`.`name` and `data`.`email` as drop-down menus in `shopping`.
>
> This is InnoDB so I should be able to do this by SQL, right?

where do you want to see this drop-downs?

MySQL is an database server, and i m not aware of any place where it would
display any drop-downs, possible you speak of some sort of GUI, like MySQL
Admin or phpMyAdmin?


--
Sebastian

Sorry, I'm a top quoter. This is what I want to do. I'm still told there
re problems with my keys.

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
`person_id` int(11) NOT NULL auto_increment,
`name` varchar(255) default NULL,
`email` varchar(255) default NULL,
PRIMARY KEY (`person_id`),
KEY `email` (`email`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 1, 'Name',
'email@address.com' ) ;
INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 2, 'second
Name', 'email@anotheraddress.com' ) ;

DROP TABLE IF EXISTS `shopping`;
CREATE TABLE IF NOT EXISTS `shopping` (
`shopping_id` int(11) NOT NULL,
`email` varchar(255) default NULL,
`name` varchar(255) default NULL,
PRIMARY KEY (`shopping_id`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `name` (`name`),
FOREIGN KEY (`email`) REFERENCES `person` (`email`),
FOREIGN KEY (`name`) REFERENCES `person` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Sebastian Mendel wrote:
>>
>> This is InnoDB so I should be able to do this by SQL, right?
>
> where do you want to see this drop-downs?

See below...

> -----Original Message-----
> From: John Taylor-Johnston [mailto:John.Taylor-
> Johnston@cegepsherbrooke.qc.ca]
> Sent: Thursday, March 20, 2008 2:17 PM
> To: Sebastian Mendel; mysql@lists.mysql.com
> Subject: Re: relational tables
>
> DROP TABLE IF EXISTS `person`;
> CREATE TABLE `person` (
> `person_id` int(11) NOT NULL auto_increment,
> `name` varchar(255) default NULL,
> `email` varchar(255) default NULL,
> PRIMARY KEY (`person_id`),
> KEY `email` (`email`),
> KEY `name` (`name`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
>
> INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 1,
> 'Name',
> 'email@address.com' ) ;
> INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 2,
> 'second
> Name', 'email@anotheraddress.com' ) ;
>
> DROP TABLE IF EXISTS `shopping`;
> CREATE TABLE IF NOT EXISTS `shopping` (
> `shopping_id` int(11) NOT NULL,
> `email` varchar(255) default NULL,
> `name` varchar(255) default NULL,
> PRIMARY KEY (`shopping_id`),
> UNIQUE KEY `email` (`email`),
> UNIQUE KEY `name` (`name`),
> FOREIGN KEY (`email`) REFERENCES `person` (`email`),
> FOREIGN KEY (`name`) REFERENCES `person` (`name`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>

Something doesn't smell right with the 'shopping' table. Rather than using
what appears to be the same values (aside from the person_id as opposed to
the shopping_id), wouldn't it be more effective to have the shopping table
have 'shopping_id' and 'person_id' fields? With that approach, you could
get away from having the same data in two tables. But, you'll have to start
building ways to look up the person_id based on name and email values in
order to populate the shopping table.

Took a flyer at an updated version of the above (not knowing what your
intent is):

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
`person_id` int(11) NOT NULL auto_increment,
`name` varchar(255) default NULL,
`email` varchar(255) default NULL,
PRIMARY KEY (`person_id`),
KEY `email` (`email`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( null, 'Name',
'email@address.com' ) ; -- updated the key value to allow for being
created automatically
INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( null,
'second Name', 'email@anotheraddress.com' ) ; -- same thing here

DROP TABLE IF EXISTS `shopping`;
CREATE TABLE IF NOT EXISTS `shopping` (
`shopping_id` int(11) NOT NULL,
`person_id` int(11) NOT NULL,
PRIMARY KEY (`shopping_id`),
FOREIGN KEY (`person_id`) REFERENCES `person` (`person_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The syntax of the above might not be 100%...but it looks to be close enough.

No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.21.7/1335 - Release Date: 3/19/2008
9:54 AM