List:General Discussion« Previous MessageNext Message »
From:bruce Date:July 4 2004 11:16pm
Subject:RE: mysql sql question
View as plain text  
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:Quentin.Bennett@stripped]
Sent: Sunday, July 04, 2004 3:49 PM
To: bedouglas@stripped
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:bedouglas@stripped]
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:Quentin.Bennett@stripped]
Sent: Sunday, July 04, 2004 3:41 PM
To: bedouglas@stripped; Emmett Bishop; mysql@stripped
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:bedouglas@stripped]
Sent: Monday, 5 July 2004 10:41 a.m.
To: 'Emmett Bishop'; mysql@stripped
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:bedouglas@stripped]
Sent: Sunday, July 04, 2004 3:26 PM
To: 'Emmett Bishop'; mysql@stripped
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:dyne_erg@stripped]
Sent: Sunday, July 04, 2004 12:59 PM
To: bedouglas@stripped
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 <bedouglas@stripped> 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/mysql?unsub=1
>
>





__________________________________
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/mysql?unsub=1


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1

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/mysql?unsub=1

Thread
date and time functions.Hari Yellina4 Jul
Re: date and time functions.Eric Bergen4 Jul
  • mysql sql questionbruce4 Jul
    • Re: mysql sql questionPaul DuBois4 Jul
      • RE: mysql sql questionbruce5 Jul
    • Re: mysql sql questionPeter Brawley6 Jul
RE: mysql sql questionbruce5 Jul
  • RE: mysql sql questionbruce5 Jul
RE: mysql sql questionQuentin Bennett5 Jul
  • RE: mysql sql questionbruce5 Jul
RE: mysql sql questionQuentin Bennett5 Jul
  • RE: mysql sql questionbruce5 Jul
    • mysql sql files...bruce5 Jul
      • RE: mysql sql files...bruce5 Jul
      • Re: mysql sql files...Paul DuBois5 Jul
    • Re: mysql sql questionJohn Hicks5 Jul
Re: mysql sql questionSGreen7 Jul