RE: relational tables
See below... > -Original Message- > From: John Taylor-Johnston [mailto:John.Taylor- > [EMAIL PROTECTED] > 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 PROTECTED]' ) ; > INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 2, > 'second > Name', '[EMAIL PROTECTED]' ) ; > > 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 PROTECTED]' ) ; -- updated the key value to allow for being created automatically INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( null, 'second Name', '[EMAIL PROTECTED]' ) ; -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: relational tables
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 PROTECTED]' ) ; INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 2, 'second Name', '[EMAIL PROTECTED]' ) ; 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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: relational tables
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: relational tables query help
because there isn't any partners table in the FROM clause! --- Justin French <[EMAIL PROTECTED]> ha scritto: > Hi all, > > I have three tables: > > partners (id,name,url,desc) > services (id,desc,category) > partner_service_rel (pid,sid) > > Each partner offers a range of services, and each > services has an id, > description, and category. > > Now, it's EASY to search for partners that offer a > certain service, but I > can't figure out a smart way to list all services > for a specific partnerID, > grouped by category. > > I got this far (which I think is perhaps WAY off > track!!): > > SELECT services.*, partners.id > FROM services, partner_service_rel > WHERE partner_service_rel.pid='1' > AND partners.id=partner_service_rel.sid > > And I'm getting "Unknown table 'partners' in field > list". > > Any advice warmly received :) > > > Justin French > > > sql,query > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list > archive) > > To request this thread, e-mail > <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > __ Yahoo! Cellulari: loghi, suonerie, picture message per il tuo telefonino http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: relational tables
* Javier Gonzalo Gloria Medina > How I crate this relational tables with mysql. Using the CREATE TABLE statement: http://www.mysql.com/doc/C/R/CREATE_TABLE.html > > where the two tables must be related by id ok You combine the tables with the SELECT statement, you don't need to do anything special when you create the tables to tell mysql that the tables are related. http://www.mysql.com/doc/S/E/SELECT.html > -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
