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....
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....
i'm not a db guy by any stretch.. but this should scale to handle 100s of
universities with no prob...
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]
>
>
__________________________________
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
