On Sunday 04 July 2004 07:16 pm, bruce wrote: > quentin/emmett... > > my question .... > > it appears that mysql essentially take the two > values returned from the select, and inserts them > into the (name,collegeid) that i specified... > > if this is the case, then i'm cool....
Yes, this is the case. This is all explained in the FM at: http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html > mind if i ask you antoher question from a design > perspective... > > i'm creating a system with college class schedule > information.. it needs to present the user with the > following information: > > state > university name > school name (school of > medicine/engineering/accounting/etc..) dept name > course/class name > class section > class day/time > instructor > > rather than put all this into one large table.. > > i'm considering having separate tbls for each item. > each tabole would be connected via the id of the > parent. > > tables... > > stateTbl > stateName - unique > stateID > > universityTbl > universityName - unique > universityID > stateID > > schoolTbl > schoolName - (unique for schoolName and > universityID) schoolID > universityID > > deptTbl > deptName - (unique for deptName and universityID) > deptID > universityID > > courseTbl > courseName - (unique for courseName, classSection > and deptID) courseID > deptID > classSection - char > classday > classtime > instructorID > > instructorTbl > name > dept > phone > email > universityID > instructorID > > > does this make sense... any > thoughts/comments/critcisms.... Almost. You're on the verge of discovering (or reinventing) normal forms. But it looks like you have combined course, class, and meeting info into a single table. These should be in three different tables for it to be properly normalized. The general rule is each piece of information should be stored only once in the database. Your plan stores course name multiple times (sections * meeting times). This is not normal :( > i'm not a db guy by any stretch.. but this should > scale to handle 100s of universities with no prob... You are correct. Good luck, --John > thanks > > -bruce > > > -----Original Message----- > From: Quentin Bennett > [mailto:[EMAIL PROTECTED] Sent: > Sunday, July 04, 2004 3:49 PM > To: [EMAIL PROTECTED] > Cc: MySQL (E-mail) > Subject: RE: mysql sql question > > > Hi, > > The insert says 'insert data in to two columns, name > and collegeid'. > > The select says "get two columns, 'tom' and id" - > 'tom' is a fixed value, the same for each row, and > id is taken from the test table. > > If you want the name from test, then use > > mysql> insert into dept (name,collegeid) > -> select name, id from test where name="sammy"; > > > -----Original Message----- > From: bruce [mailto:[EMAIL PROTECTED] > Sent: Monday, 5 July 2004 10:50 a.m. > To: Quentin Bennett > Subject: RE: mysql sql question > > > i can't see how this would work at all... > > unless you're saying the select will return "tom" > and stuff that into the dept table as the "name" > value..... > > and where/how would the "collegeid" of the insert be > derived from.. > > -bruce > > > > -----Original Message----- > From: Quentin Bennett > [mailto:[EMAIL PROTECTED] Sent: > Sunday, July 04, 2004 3:41 PM > To: [EMAIL PROTECTED]; Emmett Bishop; > [EMAIL PROTECTED] Subject: RE: mysql sql > question > > > Hi, > > You've already specified some values, so you can't > then add a 'select' clause as well. > > Try > > mysql> insert into dept (name,collegeid) > -> select 'tom', id from test where > name="sammy"; > > HTH > > Quentin > > -----Original Message----- > From: bruce [mailto:[EMAIL PROTECTED] > Sent: Monday, 5 July 2004 10:41 a.m. > To: 'Emmett Bishop'; [EMAIL PROTECTED] > Subject: RE: mysql sql question > > > i created the following as a simple test... > > mysql> describe test; > +-------+----------+------+-----+---------+--------- >-------+ > > | Field | Type | Null | Key | Default | Extra > | | > > +-------+----------+------+-----+---------+--------- >-------+ > > | name | char(20) | YES | MUL | NULL | > | | id | int(10) | | PRI | NULL | > | auto_increment | > > +-------+----------+------+-----+---------+--------- >-------+ 2 rows in set (0.00 sec) > > mysql> describe dept; > +-----------+----------+------+-----+---------+----- >-----------+ > > | Field | Type | Null | Key | Default | > | Extra | > > +-----------+----------+------+-----+---------+----- >-----------+ > > | name | char(20) | YES | MUL | NULL | > | | collegeid | int(10) | YES | | > | NULL | | nameid | int(10) | > | | PRI | NULL | auto_increment | > > +-----------+----------+------+-----+---------+----- >-----------+ 3 rows in set (0.00 sec) > > mysql> select * from test; > +--------+----+ > > | name | id | > > +--------+----+ > > | sa | 1 | > | be | 2 | > | sam1ss | 3 | > | sammy | 4 | > > +--------+----+ > 4 rows in set (0.00 sec) > > mysql> select * from dept; > Empty set (0.00 sec) > > > i tried... > mysql> insert into dept (name,collegeid) values > ('tom',test.id) -> select id from test where > name="sammy"; and got the following error... > > ERROR 1064: You have an error in your SQL syntax. > Check the manual that corresponds to your MySQL > server version for the right syntax to use near > 'select id from test where name="sammy"' at line 2 > > i've tried a number of different iterations.. any > ideas as to why this doesn't seem to work...???? > > thanks.... > > -bruce > > ps. also, if i do/can manage to get this to work, > shouldn't it be possible to extend the basic > approach to multiple tables, using FROM tbl1, tbl2, > tbl3.. > > ----------------------------- > > > -----Original Message----- > From: bruce [mailto:[EMAIL PROTECTED] > Sent: Sunday, July 04, 2004 3:26 PM > To: 'Emmett Bishop'; [EMAIL PROTECTED] > Subject: RE: mysql sql question > > > what you provided would almost do it... but i want > to insert into the "dog table" the name that i > submit... > > i'd like to do something like.... > > if i submit dogname, ownername > > insert into dog (name, ownerid) values ($dogname, > owner.id) select ownerid from owner where owner.name > = $ownername; > > -bruce > > > > -----Original Message----- > From: Emmett Bishop [mailto:[EMAIL PROTECTED] > Sent: Sunday, July 04, 2004 12:59 PM > To: [EMAIL PROTECTED] > Subject: Re: mysql sql question > > > Bruce, > > what you want it the insert into ... select > statement. > > It's like this: > > insert into dog (name, ownerid) > select name, ownerid from owner where ownerid = 8; > > Does that sound like what you need? > > -- Tripp > > --- bruce <[EMAIL PROTECTED]> wrote: > > hi... > > > > i have two hypothetical tables > > create table owner ( > > -> name char(20) , > > -> ownerid int(10) auto_increment primary > > key); > > > > create table dog ( > > -> name char(20) , > > -> ownerid int(10), > > -> dogid int(10) auto_increment primary key); > > > > i'm curious as to how i'd go about inserting a > > name and the id of the owner, > > in table "dog", in a single sql statement. > > > > something like this psuedo sql.. > > insert table (name, ownerid) values ($name, > > $ownerid) > > where owner.owner = owner > > > > in other words, an app would supply the values for > > the "dog name", and the > > "owner". the sql would be able to derive the > > "ownerid" for the "owner" from > > the owner table, and then be able to insert the > > "ownerid", and "dogname" > > into the dog table... > > > > searching through google gets me to being able to > > write a php/perl script > > where i can do this using multiple sql > > statements.. but i'm trying to see > > how to do it in a single statement... > > > > any comments/criticisms would be helpful... > > > > thanks... > > > > -bruce > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] >m > > > > > > > > __________________________________ > Do you Yahoo!? > New and Improved Yahoo! Mail - 100MB free storage! > http://promotions.yahoo.com/new_mail > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] >nk.net > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] >nfinity.co.nz > > The information contained in this email is > privileged and confidential and intended for the > addressee only. If you are not the intended > recipient, you are asked to respect that > confidentiality and not disclose, copy or make use > of its contents. If received in error you are asked > to destroy this email and contact the sender > immediately. Your assistance is appreciated. The > information contained in this email is privileged > and confidential and intended for the addressee > only. If you are not the intended recipient, you are > asked to respect that confidentiality and not > disclose, copy or make use of its contents. If > received in error you are asked to destroy this > email and contact the sender immediately. Your > assistance is appreciated. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] >nk.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
