Re: basic question about joins....
On Sunday 04 July 2004 11:36 pm, bruce wrote: > hi... > > i'm trying to figure out how to deal with joins > (left/right).. > > i have the following test tbls... > > create table universityTBL( > name varchar(50) not null default '', > ID int(10) not null auto_increment, > primary key (ID), > unique key (name) > )type =MyISAM; > > > create table schoolTBL( > name varchar(50) not null default '', > universityID int(10) not null, > ID int(10) not null auto_increment, > primary key (ID), > unique key (name, universityID) > )type =MyISAM; > > mysql> describe universityTBL; > +---+-+--+-+-+-- >--+ > > | Field | Type| Null | Key | Default | Extra > | | > > +---+-+--+-+-+-- >--+ > > | name | varchar(50) | | UNI | | > | | ID| int(10) | | PRI | NULL > || auto_increment | > > +---+-+--+-+-+-- >--+ 2 rows in set (0.00 sec) > > mysql> describe schoolTBL; > +--+-+--+-+- >++ > > | Field| Type| Null | Key | Default > | | Extra | > > +--+-+--+-+- >++ > > | name | varchar(50) | | MUL | > | || universityID | int(10) | > | | | 0 || ID > | | int(10) | | PRI | NULL| > | auto_increment | > > +--+-+--+-+- >++ 3 rows in set (0.00 sec) > > mysql> select * from universityTBL; > +--++ > > | name | ID | > > +--++ > > | sam | 1 | > | bed | 2 | > > +--++ > 2 rows in set (0.00 sec) > > mysql> select * from schoolTBL; > +--+--++ > > | name | universityID | ID | > > +--+--++ > > | medicine |1 | 1 | > > +--+--++ > > > i want to be able to produce a select where > schoolTBL.universityID = university.ID. > > i can get the results using a straight select with a > where" and a "and" clause: this works... > mysql> select s1.name,u1.name > -> from universityTBL as u1, schoolTBL as s1 > -> where u1.ID=s1.universityID > -> and u1.name='sam'; > +--+--+ > > | name | name | > > +--+--+ > > | medicine | sam | > > +--+--+ > 1 row in set (0.00 sec) > > > however, i'm trying to get the results using a join. > i've tried the follwoing with no luck.. mysql> > select schoolTBL.name, universityTBL.name -> from > universityTBL , schoolTBL > -> left join universityTBL on > universityTBL.ID=schoolTBL.universityID -> where > universityTBL.name='sam'; > ERROR 1066: Not unique table/alias: 'universityTBL' > > any thoughts/comments as to what's wrong... it's got > to be something basic... perusing through > google/mysql/etc.. hasn't shed any light on where > the issue is... > > thanks for any comments/criticisms/etc... > > =bruce You were using a join with your "straight select": an inner join. What you seem to be looking for is a left join. Wesley's post speaks to that point. (The difference is, the inner join would not give you results for schools that did not have a university associated with them. A left join will give you results for such schools, with nulls in the columns for the university.) --John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and long semaphore waits
Mitch, please send the FULL .err log to me. Best regards, Heikki - Original Message - From: "Mitch Pirtle" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Saturday, July 03, 2004 4:41 PM Subject: InnoDB and long semaphore waits > Hi listers, > > I just got here, so please let me know if this is not the appropriate > list! :) > > Running MySQL 4.0.20 on Fedora Core 1, with InnoDB tables. Installed > from RPMs provided at MySQL.com. > > Last night the beastie came down hard, and requred a physical reboot in > order to free/kill some mysqld processes. I say this as I worry that it > could be from a hardware error, however it is running on a dual Xeon > machine that is not even 6 months old... > > When I attempted to get the stack trace I only get "nm: > /usr/sbin/mysqld: no symbols", and the docs point at stuff that is not > on this box. ? > > I see two errors that need resolution: > > -- --- > > 1) In the error log, I see the following: > > InnoDB: ## Diagnostic info printed to the standard error stream > InnoDB: Warning: a long semaphore wait: > --Thread 23207961 has waited at btr0sea.c line 480 for 625.00 seconds > the semaphore: > X-lock on RW-latch at 0x4506a768 created in file btr0sea.c line 139 > a writer (thread id 23207961) has reserved it in mode wait exclusive > number of readers 1, waiters flag 1 > Last time read locked in file btr0sea.c line 745 > Last time write locked in file btr0sea.c line 480 > InnoDB: Error: semaphore wait has lasted > 600 seconds > InnoDB: We intentionally crash the server, because it appears to be hung. > 040702 20:45:27InnoDB: Assertion failure in thread 24583 in file > sync0arr.c line 925 > InnoDB: We intentionally generate a memory trap. > InnoDB: Submit a detailed bug report to http://bugs.mysql.com. > InnoDB: If you get repeated assertion failures or crashes, even > InnoDB: immediately after the mysqld startup, there may be > InnoDB: corruption in the InnoDB tablespace. See section 6.1 of > InnoDB: http://www.innodb.com/ibman.php about forcing recovery. > mysqld got signal 11; > > ...and goes on to say: > > key_buffer_size=402653184 > read_buffer_size=2093056 > max_used_connections=176 > max_connections=500 > threads_connected=146 > It is possible that mysqld could use up to > key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections > = 2439212 K > bytes of memory > Hope that's ok; if not, decrease some variables in the equation. > > thd=(nil) > Attempting backtrace. You can use the following information to find out > where mysqld died. If you see no messages after this, something went > terribly wrong... > Cannot determine thread, fp=0xbfedf758, backtrace may not be correct. > Stack range sanity check OK, backtrace follows: > 0x80720d4 > 0x8250d48 > 0x81ed044 > 0x80f9148 > 0x824e4fc > 0x828452a > New value of fp=(nil) failed sanity check, terminating stack trace! > > Again, I try to follow the instructons on dealing with the stack trace > but the instructions fail, and also do not provide enough explanation > for me to figure out on my own how to fix it :( > > -- --- > 2) After rebooting the server, the error log fills up with: > > 040703 9:07:59 Aborted connection 55 to db: 'db1' user: 'www' host: > `192.168.1.1' (Got an error reading communication packets) > ...and it repeats itself roughly every 5-to-10 seconds, which I > obviously find alarming. > > Cannot find any reference on that error, and don't really have enough > data to know how to react... > > > Can somebody please whack me upside the head with a cluestick? > > -- Mitch > > -- > 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]
Re: basic question about joins....
Bruce: The problem is that you have universityTBL listed twice in your list of tables. Here is your statement, with numbers for each of the sources. SELECT schoolTBL.name, universityTBL.name FROM (1) universityTBL, (2) schoolTBL LEFT JOIN (3) universityTBL ON schoolTBL.universityID = universityTBL.ID WHERE universityTBL.name = 'sam' The syntax I think you want is: SELECT schoolTBL.name, universityTBL.name FROM schoolTBL LEFT JOIN universityTBL ON schoolTBL.universityID = universityTBL.ID WHERE universityTBL.name = 'sam' Wes On Jul 4, 2004, at 11:36 PM, bruce wrote: hi... i'm trying to figure out how to deal with joins (left/right).. i have the following test tbls... create table universityTBL( name varchar(50) not null default '', ID int(10) not null auto_increment, primary key (ID), unique key (name) )type =MyISAM; create table schoolTBL( name varchar(50) not null default '', universityID int(10) not null, ID int(10) not null auto_increment, primary key (ID), unique key (name, universityID) )type =MyISAM; mysql> describe universityTBL; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | name | varchar(50) | | UNI | || | ID| int(10) | | PRI | NULL| auto_increment | +---+-+--+-+-++ 2 rows in set (0.00 sec) mysql> describe schoolTBL; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | name | varchar(50) | | MUL | || | universityID | int(10) | | | 0 || | ID | int(10) | | PRI | NULL| auto_increment | +--+-+--+-+-++ 3 rows in set (0.00 sec) mysql> select * from universityTBL; +--++ | name | ID | +--++ | sam | 1 | | bed | 2 | +--++ 2 rows in set (0.00 sec) mysql> select * from schoolTBL; +--+--++ | name | universityID | ID | +--+--++ | medicine |1 | 1 | +--+--++ i want to be able to produce a select where schoolTBL.universityID = university.ID. i can get the results using a straight select with a where" and a "and" clause: this works... mysql> select s1.name,u1.name -> from universityTBL as u1, schoolTBL as s1 -> where u1.ID=s1.universityID -> and u1.name='sam'; +--+--+ | name | name | +--+--+ | medicine | sam | +--+--+ 1 row in set (0.00 sec) however, i'm trying to get the results using a join. i've tried the follwoing with no luck.. mysql> select schoolTBL.name, universityTBL.name -> from universityTBL , schoolTBL -> left join universityTBL on universityTBL.ID=schoolTBL.universityID -> where universityTBL.name='sam'; ERROR 1066: Not unique table/alias: 'universityTBL' any thoughts/comments as to what's wrong... it's got to be something basic... perusing through google/mysql/etc.. hasn't shed any light on where the issue is... thanks for any comments/criticisms/etc... =bruce -- 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]
basic question about joins....
hi... i'm trying to figure out how to deal with joins (left/right).. i have the following test tbls... create table universityTBL( name varchar(50) not null default '', ID int(10) not null auto_increment, primary key (ID), unique key (name) )type =MyISAM; create table schoolTBL( name varchar(50) not null default '', universityID int(10) not null, ID int(10) not null auto_increment, primary key (ID), unique key (name, universityID) )type =MyISAM; mysql> describe universityTBL; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | name | varchar(50) | | UNI | || | ID| int(10) | | PRI | NULL| auto_increment | +---+-+--+-+-++ 2 rows in set (0.00 sec) mysql> describe schoolTBL; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | name | varchar(50) | | MUL | || | universityID | int(10) | | | 0 || | ID | int(10) | | PRI | NULL| auto_increment | +--+-+--+-+-++ 3 rows in set (0.00 sec) mysql> select * from universityTBL; +--++ | name | ID | +--++ | sam | 1 | | bed | 2 | +--++ 2 rows in set (0.00 sec) mysql> select * from schoolTBL; +--+--++ | name | universityID | ID | +--+--++ | medicine |1 | 1 | +--+--++ i want to be able to produce a select where schoolTBL.universityID = university.ID. i can get the results using a straight select with a where" and a "and" clause: this works... mysql> select s1.name,u1.name -> from universityTBL as u1, schoolTBL as s1 -> where u1.ID=s1.universityID -> and u1.name='sam'; +--+--+ | name | name | +--+--+ | medicine | sam | +--+--+ 1 row in set (0.00 sec) however, i'm trying to get the results using a join. i've tried the follwoing with no luck.. mysql> select schoolTBL.name, universityTBL.name -> from universityTBL , schoolTBL -> left join universityTBL on universityTBL.ID=schoolTBL.universityID -> where universityTBL.name='sam'; ERROR 1066: Not unique table/alias: 'universityTBL' any thoughts/comments as to what's wrong... it's got to be something basic... perusing through google/mysql/etc.. hasn't shed any light on where the issue is... thanks for any comments/criticisms/etc... =bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql sql files...
At 17:21 -0700 7/4/04, bruce wrote: hi.. i can run sql files that have sql statements by : shell:>mysql -u -p is there a way to run foo.sql if i'm already inside the mysql env... neither a search of google/mysql indicates that you can... You don't have to use Google. Just read the section on the mysql program in the MySQL Reference Manual. http://dev.mysql.com/doc/mysql/en/mysql.html In particular, this subsection: http://dev.mysql.com/doc/mysql/en/Batch_Commands.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem Starting mysqld on Tru64
Hi, Customer has found that port 3306 was open on another server in the cluster, and that seems to be what was causing it. Thanks for listening. Quentin -Original Message- From: Quentin Bennett [mailto:[EMAIL PROTECTED] Sent: Monday, 5 July 2004 9:52 a.m. To: MySQL (E-mail) Subject: Problem Starting mysqld on Tru64 Hi, A previously stable of "Ver 4.0.13-max for dec-osf5.1 on alphaev67" install on Tru64 today decided not to restart, with the error: 040705 9:31:52 Can't start server: Bind on TCP/IP port: Address already in use 040705 9:31:52 Do you already have another mysqld server running on port: 3306 ? 040705 9:31:52 Aborting The server was rebooted to ensure no stray processes were holding 3306 open. netstat -a showed nothing on that port. We changed the port to be 3307, and the server is working OK. Does anyone have any method for finding out why port 3306 would not bind. Quentin Bennett 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]
Re: mysql sql question
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 |
Re: mySQL LIMIT and ORDER BY Problem???
On Sunday 04 July 2004 02:52 am, Gary Mack wrote: > Hi there, > > I recently learned about LIMIT so that I can page > through records on a web page I am creating. > However, when coupled with ORDER BY, the sorting > does not work anymore. Can someone look at my below > query and point out what I am doing wrong? Thanks. > This is my first time posting to this list, so I > hope I have the right place. I want to sort by the > client's last name and then by the staff person's > last name. > > $qClients = "SELECT * FROM clients, staff WHERE > clients.AssignedTo = staff.StaffID ORDER BY > clients.LastName, staff.StaffLastName ASC LIMIT " . > $limitStart . "," . $recordsPerPage; > > > Gary Looks good to me. Can you be more specific about what the problem is? --John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql sql files...
to clairfy it looks as though the "source" function/command allows you to use the script from inside the mysql env... disreagrd -bruce -Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: Sunday, July 04, 2004 5:22 PM To: [EMAIL PROTECTED] Subject: mysql sql files... hi.. i can run sql files that have sql statements by : shell:>mysql -u -p 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]
mysql sql files...
hi.. i can run sql files that have sql statements by : shell:>mysql -u -p http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql sql question
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 y
RE: mysql sql question
hey
i tried your suggestion.. and it worked... could you walk me through why it
worked...
also, could i do the same basic thing if i wanted to get values from
different levels of parent tables
ie, i want to insert a name/id in table1, however, i need to get the id from
a parent tbl, which in turn is based on a parent tbl...
thanks...
-bruce
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
>
>
> --
> M
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
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
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]
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]
RE: mysql sql question
my bad... should have mentioned that both the owner and dog name are unique, but one owner can have multiple dogs... so just how does the "insert ... select" work.. it looks like it could actually do what i need, but i can't seem to get it working correctly.. -bruce -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Sunday, July 04, 2004 12:59 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: mysql sql question At 12:54 -0700 7/4/04, bruce 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 This won't work, because there is no guarantee that the name of your owner is unique. Some techniques that might be useful in this situation can be found here: http://www.onlamp.com/pub/a/onlamp/excerpt/mysqlckbk/index2.html > >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... You can't, for the reason noted above. If you happen to have a unique index on the owner.name column, then what you might want to try is the INSERT INTO ... SELECT FROM form of INSERT. http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html > >any comments/criticisms would be helpful... > >thanks... > >-bruce -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem Starting mysqld on Tru64
Hi, A previously stable of "Ver 4.0.13-max for dec-osf5.1 on alphaev67" install on Tru64 today decided not to restart, with the error: 040705 9:31:52 Can't start server: Bind on TCP/IP port: Address already in use 040705 9:31:52 Do you already have another mysqld server running on port: 3306 ? 040705 9:31:52 Aborting The server was rebooted to ensure no stray processes were holding 3306 open. netstat -a showed nothing on that port. We changed the port to be 3307, and the server is working OK. Does anyone have any method for finding out why port 3306 would not bind. Quentin Bennett 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 LIMIT and ORDER BY Problem???
Hi there, I recently learned about LIMIT so that I can page through records on a web page I am creating. However, when coupled with ORDER BY, the sorting does not work anymore. Can someone look at my below query and point out what I am doing wrong? Thanks. This is my first time posting to this list, so I hope I have the right place. I want to sort by the client's last name and then by the staff person's last name. $qClients = "SELECT * FROM clients, staff WHERE clients.AssignedTo = staff.StaffID ORDER BY clients.LastName, staff.StaffLastName ASC LIMIT " . $limitStart . "," . $recordsPerPage; Gary
Report query problem
Coming from an Oracle background I am struggling with the following report generation. This is a report which needs to be created using mySQL: Week of mm/dd/yy mm/dd/yy mm/dd/yy mm/dd/yy mm/dd/yy Total All Groups nn nn nn nn nn Group ann nn nn nn nn Group bnn nn nn nn nn Group cnn nn nn nn nn - The tables are: EventLog cols: userlogin, groupname, eventdate, Ipaddress Group cols: groupname Problem: My attempts to build this report have been to create five separate tempory tables aggregating the five separate weekly columns and then doing a large join to merge the five reports. I cannot get this working. Any suggestions?
Re: mysql sql question
At 12:54 -0700 7/4/04, bruce 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 This won't work, because there is no guarantee that the name of your owner is unique. Some techniques that might be useful in this situation can be found here: http://www.onlamp.com/pub/a/onlamp/excerpt/mysqlckbk/index2.html 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... You can't, for the reason noted above. If you happen to have a unique index on the owner.name column, then what you might want to try is the INSERT INTO ... SELECT FROM form of INSERT. http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html any comments/criticisms would be helpful... thanks... -bruce -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql sql question
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]
Re: date and time functions.
Date modification functions are listed in the manual here: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html If you are just trying to add a year to a column try something like this. update table set col = col + interval 1 year; -Eric On Sun, 4 Jul 2004 18:22:04 +1000, Hari Yellina <[EMAIL PROTECTED]> wrote: > Hi All, > > I trying to add one year to date function is there a good documentation on > how we can manipulate on date function. > > Changing the format of dates for mysql. > > Thank you , > > Harry > > > -- > 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]
RE: User Logon Procedure Fails
I still get an error and think this line
is to blame:
AND
password=UserPassword('$_POST[TXT_UserPassword]')";
I’ve changed it to:
AND UserPassword=('$_POST[TXT_UserPassword]')";
And I still get erros but further down the
code. I’ll work through those.
BTW: didn’t know that “password”
was a reserved word… oops…!
Michael
Mason
Business Support Services
Arras® People
Tel: 01706 342310
Mobile: 07793 782287
Fax: 01706 642754
Member of the Recruitment Employment Confederation (00052055)
The views expressed in this mail are
entirely those of the sender, and do not necessarily represent the views or
position of Arras Services Ltd. The information contained in this communication
is confidential and may be legally privileged. It is intended solely for the
use of the individual or entity to whom it is addressed and others authorised
to receive it. If you are not the intended recipient you are hereby notified
that any disclosure, copying, distribution or taking any action in relation to
the contents of this information is strictly prohibited and may be unlawful.
Neither the sender nor the represented institution is liable for the correct
and complete transmission of the contents of this e-mail, or for its timely
receipt.
From: Michael Mason [mailto:[EMAIL PROTECTED]]
Sent: 04 July 2004 13:19
To: MySQL
Mailing List
Subject: User Logon Procedure
Fails
Another day another problem. This time it appears that users are able
to
enter their details but I get a query execution error with the following
section of code:
/* Verify Login */
$sql = "SELECT UserFirstName,UserID,UserPassword FROM
RegisteredMembers
WHERE UserID='$_POST[TXT_UserID]'";
$result = mysql_query($sql) or die ("couldn't select
database");
$num = mysql_num_rows($result);
if ($num == 1) //Login Name Was Found
{
$sql = "SELECT UserID FROM RegisteredMembers
WHERE UserID='$_POST[TXT_UserID]'
AND
password=UserPassword('$_POST[TXT_UserPassword]')";
$result2 = mysql_query($sql) or die("Couldn't execute query
#2.");
The next to last line is bugging me though. "AND password= etc. because I
have a variable declared earlier in the logon page for TXT_UserPassword and
the UserPassword column exists but where in the hell is
"password"...?
Is this another of MySQLs "on the fly" variables...?
Michael Mason
Business Support Services
Arras® People
Tel: 01706 342310
Mobile: 07793
782287
Fax: 01706 642754
Member of the Recruitment Employment Confederation
(00052055)
The views expressed in this mail are entirely those of the sender,
and do not necessarily represent the views or position of Arras Services Ltd.
The information contained in this communication is confidential and may be
legally privileged. It is intended solely for the use of the individual or
entity to whom it is addressed and others authorised to receive it. If you are
not the intended recipient you are hereby notified that any disclosure,
copying, distribution or taking any action in relation to the contents of this
information is strictly prohibited and may be unlawful. Neither the sender nor
the represented institution is liable for the correct and complete transmission
of the contents of this e-mail, or for its timely receipt.
User Logon Procedure Fails
Another day another problem. This time it appears that users are able
to
enter their details but I get a query execution error with the following
section of code:
/* Verify Login */
$sql = "SELECT UserFirstName,UserID,UserPassword FROM
RegisteredMembers
WHERE UserID='$_POST[TXT_UserID]'";
$result = mysql_query($sql) or die ("couldn't select
database");
$num = mysql_num_rows($result);
if ($num == 1) //Login Name Was Found
{
$sql = "SELECT UserID FROM RegisteredMembers
WHERE UserID='$_POST[TXT_UserID]'
AND
password=UserPassword('$_POST[TXT_UserPassword]')";
$result2 = mysql_query($sql) or die("Couldn't execute query
#2.");
The next to last line is bugging me though. "AND password= etc. because I
have a variable declared earlier in the logon page for TXT_UserPassword and
the UserPassword column exists but where in the hell is
"password"...?
Is this another of MySQLs "on the fly" variables...?
Michael Mason
Business Support Services
Arras® People
Tel: 01706 342310
Mobile: 07793
782287
Fax: 01706 642754
Member of the Recruitment Employment Confederation
(00052055)
The views expressed in this mail are entirely those of the
sender, and do not necessarily represent the views or position of Arras
Services Ltd. The information contained in this communication is confidential
and may be legally privileged. It is intended solely for the use of the
individual or entity to whom it is addressed and others authorised to receive
it. If you are not the intended recipient you are hereby notified that any
disclosure, copying, distribution or taking any action in relation to the
contents of this information is strictly prohibited and may be unlawful.
Neither the sender nor the represented institution is liable for the correct
and complete transmission of the contents of this e-mail, or for its timely
receipt.
Re: MySQL 4.1 - Full-Text using UTF-8
Mabye it is something connected to my.ini definitions? mysql> SHOW VARIABLES LIKE 'character%'; +--+ -+ | Variable_name| Value | +--+ -+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | C:\Program Files\Apache Group\mysql\share\charsets/ | +--+ -+ 7 rows in set (0.01 sec) -thanks, Lorderon. ""Lorderon"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, > > How do I search and index a TEXT column to use the UTF-8 charset? > Do I need to define the FULLTEXT index or the column definition in a special > way? > I tried to use it as usual as I use full-text search on English only, but it > seems not to match... (it's not the ft_min_word_len or the 50% treshold).. > > -thanks, Lorderon. > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 - Full-Text using UTF-8
Hi, How do I search and index a TEXT column to use the UTF-8 charset? Do I need to define the FULLTEXT index or the column definition in a special way? I tried to use it as usual as I use full-text search on English only, but it seems not to match... (it's not the ft_min_word_len or the 50% treshold).. -thanks, Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld 4.0.20 ICC compiled by mysql.com quits with signal 11
>Description: I installed mysql 4.0.20 (ICC Intel Compiler compiled mysql, compiled by mysql.com) and when starting mysqld it immediately exits with signal 11 and wrote this into mysqld-log: 040703 23:24:30 mysqld started mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x84a4d10 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xf0efeeed, backtrace may not be correct. Bogus stack limit or frame pointer, fp=0xf0efeeed, stack_bottom=0xc000, thread_stack=196608, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0xecebeae9 is invalid pointer thd->thread_id=139089296 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 040703 23:24:30 mysqld ended >How-To-Repeat: I just need to start mysqld. >Fix: no idea >Submitter-Id: unknown >Originator: Christian >Organization: none >MySQL support: none >Synopsis: mysqld 4.0.20 ICC compiled by mysql.com quits with signal 11 >Severity: critical >Priority: high >Category: mysql >Class: sw-bug >Release: mysql-4.0.20-standard (Official MySQL-standard binary) >C compiler:8.0 >C++ compiler: 8.0 >Environment: gentoo linux, intel mainboard, pentium 4, IDE HDD, 1024 MB RAM System: Linux server5 2.4.26 #2 SMP Do Jun 24 20:00:00 CEST 2004 i686 Intel(R) Pentium(R) 4 CPU 3.00GHz GenuineIntel GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i686-pc-linux-gnu/3.3.2/specs Configured with: /var/tmp/portage/gcc-3.3.2-r5/work/gcc-3.3.2/configure --prefix=/usr --bindi r=/usr/i686-pc-linux-gnu/gcc-bin/3.3 --includedir=/usr/lib/gcc-lib/i686-pc-l inux-gnu/3.3.2/include --datadir=/usr/share/gcc-data/i686-pc-linux-gnu/3.3 - -mandir=/usr/share/gcc-data/i686-pc-linux-gnu/3.3/man --infodir=/usr/share/g cc-data/i686-pc-linux-gnu/3.3/info --enable-shared --host=i686-pc-linux-gnu --target=i686-pc-linux-gnu --with-system-zlib --enable-languages=c,c++,f77,o bjc --enable-threads=posix --enable-long-long --disable-checking --enable-cs tdio=stdio --enable-clocale=generic --enable-__cxa_atexit --enable-version-s pecific-runtime-libs --with-gxx-include-dir=/usr/lib/gcc-lib/i686-pc-linux-g nu/3.3.2/include/g++-v3 --with-local-prefix=/usr/local --enable-shared --ena ble-nls --without-included-gettext --disable-multilib Thread model: posix gcc version 3.3.2 20031218 (Gentoo Linux 3.3.2-r5, propolice-3.3-7) Compilation info: CC='icc' CFLAGS='-O3 -unroll2 -ip -mp -no-gcc -restrict' CXX='icc' CXXFLAGS='-O3 -unroll2 -ip -mp -no-gcc -restrict' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Jun 24 18:09 /lib/libc.so.6 -> libc-2.3.2.so -rwxr-xr-x1 root root 1272572 Apr 13 07:32 /lib/libc-2.3.2.so -rw-r--r--1 root root 2707526 Apr 13 07:32 /usr/lib/libc.a -rwxr-xr-x1 root root 204 Apr 13 07:32 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-embedded-server' '--with-innodb' 'CC=icc' 'CFLAGS=-O3 -unroll2 -ip -mp -no-gcc -restrict' 'CXXFLAGS=-O3 -unroll2 -ip -mp -no-gcc -restrict' 'CXX=icc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date and time functions.
Hi All, I trying to add one year to date function is there a good documentation on how we can manipulate on date function. Changing the format of dates for mysql. Thank you , Harry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ANN: gtk2-gladexml_DBI_helper-0.1
On Sun, Jul 04, 2004 at 11:05:12 +1000, Daniel Kasak wrote: > [...] > The code is available here: > http://enthalpy.homelinux.org/code/gtk2-gladexml_DBI_helper-0.1.tar.bz2 > I'm not sure on the naming I've used at present. If someone has a better > name, feel free to offer say so. The Gtk2::Ex namespace was discussed here lately. Perhaps you should name your module to be in that namespace. Something like Gtk2::Ex::DBI. (and the archive would then be Gtk2-Ex-DBI-0.1.tar.gz) > [...] --- Jan 'Bulb' Hudec <[EMAIL PROTECTED]> signature.asc Description: Digital signature
