Posting Question
I am asking before I post so I don't anger everyone... Is this list okay to post a specific question regarding multiple row inserts.. I am doing this in perl, and I need some help with the perl part... Thanks, Taylor
Load data Infile update?
Can I perform an update on a table using load data infile..? If I have the following table... Name Score Rank John NULL NULL Susan NULL NULL And I have a data file that has... John,95,1 Susan,89,2 Any idea if this can be done? Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Text field how to handle aliases
I need help on the best way to handle a field that could have many different ways of naming something. For instance, school name Let's take Saint Joseph's University This could be Saint Joseph's Univeristy, U. of St. Joe, SJU, Univ. St. Joe, etc... In this case, I don't think I can always get what I want by doing a select from table where name like something. Is there a practical/better way to handle this kind of thing. This will come up a lot. I'm thinking right now I will have to maintain my own mapping file for each school... Something like Kansas, KU, Kansas University, University of Kansas, etc... Any ideas? Thanks, Taylor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Help
I have a mysql database/tables of information about netbackup jobs. I would like to see a count of which clients are failing more than 5 times (any status code other than 1 or 0) with the same error codes. That was easy enough. The query... select count(*) as 'attempts', client, status from bpdbjobs where (status !=0 and status !=1) group by client having count(*) 5 order by status. This produced the following type of output. attempts | client | Status 8moe 13 6win115 and so on... works great. And as long as I keep the time frame to less than one day, no problem But how would I go about breaking it down further to show me these by date if I wanted to go back further in time? For instance if I write the same query but in the select clause I add backup_date then I would like to see attempts | client | Status | Backup Date 8moe13 2004-09-13 6moe13 2004-09-14 but I actually get attempts | client | Status | Backup Date 14 moe13 2004-09-14 and it just assigns the latest backup date it saw for one of these entries. Any ideas on how to write this query? Thanks all, and if there is a better list to pose query questions please tell me which one it is. Taylor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to count columns in SQL?
Kind of klugy, but it works... Using perl or php, do a fetchrow on a table. With any query, i.e. select * from table. Now assign @row to a scalar variable. This will give you the length of the array. The length is the number of columns. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, September 16, 2004 3:18 PM To: Monet Cc: mysql Subject: Re: how to count columns in SQL? AFAIK, exposing the database schema tables/views is on the todo list for 5.x. Until then you will have to count the # of rows that comes back from describe table. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Monet [EMAIL PROTECTED] wrote on 09/16/2004 03:50:55 PM: Hi, everyone, I have a sql question here, please don't ignore it and help please. I know one can use describe table in mysql to get number of columns. Is there any way to do so in SQL? I have some tables on SQL sever 2000. I use Access 2000 Link table function to link to the server. And I want to count columns not rows since I want to know how many fields in a table. Any advise? Thanks. Monet __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! 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: Query Help
Thank you much, both you and Rhino responded quickly with same answer. Doh!, That was simple. It usually is. Thanks again. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, September 16, 2004 3:57 PM To: Lewick, Taylor Cc: [EMAIL PROTECTED] Subject: Re: Query Help Add Backup date to your SELECT clause _and_ your GROUP BY clause. SELECT count(1) as attempts, client, status, bkupdate FROM bpdjobs WHERE Status not in (0,1) GROUP BY bkupdate, status, client HAVING attempts 5 This will break your report down by date, then status, then client Shawn Green Database Administrator Unimin Corporation - Spruce Pine Lewick, Taylor [EMAIL PROTECTED] wrote on 09/16/2004 04:31:54 PM: I have a mysql database/tables of information about netbackup jobs. I would like to see a count of which clients are failing more than 5 times (any status code other than 1 or 0) with the same error codes. That was easy enough. The query... select count(*) as 'attempts', client, status from bpdbjobs where (status !=0 and status !=1) group by client having count(*) 5 order by status. This produced the following type of output. attempts | client | Status 8moe 13 6win115 and so on... works great. And as long as I keep the time frame to less than one day, no problem But how would I go about breaking it down further to show me these by date if I wanted to go back further in time? For instance if I write the same query but in the select clause I add backup_date then I would like to see attempts | client | Status | Backup Date 8moe13 2004-09-13 6moe13 2004-09-14 but I actually get attempts | client | Status | Backup Date 14 moe13 2004-09-14 and it just assigns the latest backup date it saw for one of these entries. Any ideas on how to write this query? Thanks all, and if there is a better list to pose query questions please tell me which one it is. Taylor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Table Relation Design Question
Hi all, I am having a little trouble deciding how to relate some of my tables together. I was wondering about creating one big lookup table to relate 3 or 4 tables together, but wasn't sure if that was a good id, or should I have a look up table For each pair of tables. Here is a simple example of my tables. Orgs: org_id org_name (org_id is primary key) Contacts: con_id, con_name, org_id (con_id is primary, org_id is foreign key) Events: ev_id, ev_name, org_id, con_id (ev_id is primary, con_id and org_id are foreign keys) This is centered around organizations, so every contact must belong to an org, likewise for an event. I will create an org called None in case they just want To track the occasional lone contact or internal event. But because an organization can have many contacts and many events, I was thinking of using lookup tables. I.e., Contacts are assigned to Organizations, So have a table called assigned with org_id and con_id as a composite primary key. And each is a foreign key back to the correct table... And should I have a table that links orgs and events and contacts and events, or should I have one lookup table That relates them all together, i.e. orgs contacts, and events..? To simplify, is It better to have many smaller lookup tables or one big one? Thanks, Taylor
