URGENT: Change Default Location of where Database Files get written?
I have a MySQL question that I'm hoping someone can help answer. We have a linux machine which has MySQL 5.5.8 installed. It is currently installed in this location:/opt/mysql When creating a new database, a folder (with the name of the databas) gets created in this location: /opt/mysql/data Is there any way to change the location of where data is stored? The database that I need to create is going to have over a billion records in it, so it needs to be in a specific place. I want the database folder to get created here: /science/databases/databasename Thanks for any help. Tina -- Tina Matter Web Applications Developer University of Michigan Department of Epidemiology 1415 Washington Heights, Suite 4605 Ann Arbor, MI 48109 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Query - One-to-Many question
Peter, Yes, I know there are two rows in the course_subject table with a catalog_number of 520. One has a subject of 'ME' and the other has a subject of 'MSE'. What I want is ONLY the 'ME' row (if a row exists with a subject of 'ME').If an 'ME' subject row does not exist, then I want the other one. This is just one example. There are other catalog_numbers that will have two rows. There may also be catalog_numbers that only show one row (that does not have a subject of 'ME'). FYI: I get the same results with these two statements: (subject NOT LIKE 'ME') (subject 'ME') So I didn't really see a difference. Thank you. Tina Peter Brawley wrote, On 6/26/08 6:11 PM: Tina, Even if I do this simple query, while hardcoding in a catalog_number: SELECT subject, catalog_number FROM course_subject WHERE (catalog_number = 520) AND ((subject = 'ME') OR ((course_offer_number = 1) AND (subject NOT LIKE 'ME'))) Errrm, you mean ...subject 'ME'..., don't you!? I still get two rows back... So I'm not sure what else I need in my Where Clause You said the query spec was rows with a given catalog_number AND (subject='ME' OR (course_offer_number of '1' and subject 'ME')). There are two such rows for catalog number 520. What do you want your query to return in that instance? PB - Tina Matter wrote: Even if I do a basic select (with no joins) for a given catalog_number, I still get two rows back. Even if I do this simple query, while hardcoding in a catalog_number: SELECT subject, catalog_number FROM course_subject WHERE (catalog_number = 520) AND ((subject = 'ME') OR ((course_offer_number = 1) AND (subject NOT LIKE 'ME'))) I still get two rows back: subjectcatalog_number ME520 MSE520 So I'm not sure what else I need in my Where Clause Thanks bunches. Tina Peter Brawley wrote, On 6/26/08 3:33 PM: Tina, for some reason, it still pulled all of the rows Are there multiple rows which meet your WHERE condition? If so, and if you want just one of them, your need another WHERE condition. PB - Tina Matter wrote: Peter, That was the first query I tried, but for some reason, it still pulled all of the rows. So I've been trying to come up with another solution. Any other ideas? Thanks for the reply. Tina Peter Brawley wrote, On 6/26/08 2:12 PM: Tina Basically, if the subject is ME, then I want to select that row. If there is no row for that catalog_number that has a subject of ME, then I want to grab the row that has a course_offer_number of '1' and a subject that is not equal to ME. Is this what you mean? SELECT ... FROM course_table c JOIN course_subject s ON c.course_id=s.course_id WHERE s.subject=ME OR (s.course_offer_number=1 AND s.subject ME); PB Tina Matter wrote: I have two tables: 1.) A course table (stores course_id and catalog_number) 2.) A course_subject table (stores course_id, catalog_number, subject, and course_offer_number) For each row in the course_table, there can be many rows in the course_subject table, due to cross-postings among different departments. Here is what I'd like to do. I would like to grab a list of catalog_numbers from the course_table, but I only want one row in the course_subject table. Basically, if the subject is ME, then I want to select that row. If there is no row for that catalog_number that has a subject of ME, then I want to grab the row that has a course_offer_number of '1' and a subject that is not equal to ME. I am basically only grabbing the subject field from the course_subject table. If an ME subject exists for a catalog_number, grab that one. Otherwise, grab whatever other one exists.This is assuming that there will only be one other one. Does this make sense? I'd really like to know if there's a way to do this in one query. I can probably do it in PHP with multiple selects, building my list as I go. But if there's a fancy way to do this in one query, then I'd much rather do it that way. Thanks for any help. Tina No virus found in this incoming message. Checked by AVG. Version: 8.0.101 / Virus Database: 270.4.1/1521 - Release Date: 6/26/2008 11:20 AM No virus found in this incoming message. Checked by AVG. Version: 8.0.101 / Virus Database: 270.4.1/1521 - Release Date: 6/26/2008 11:20 AM No virus found in this incoming message. Checked by AVG. Version: 8.0.101 / Virus Database: 270.4.1/1521 - Release Date: 6/26/2008 11:20 AM -- *Tina Matter* Web Software Developer Department of Mechanical Engineering University of Michigan 734-763-3184 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query - One-to-Many question
Peter, I really appreciate all the help. Unfortunately, the query you came up with still returns two rows for catalog_number = 520. I modified your query slightly to this to qualify a specific catalog_number: SELECT c.course_id,s.course_offer_number,s.subject FROM course c JOIN course_subject s ON c.course_id=s.course_id AND s.subject='ME' AND c.catalog_number = 520 UNION SELECT c.course_id,s.course_offer_number,s.subject FROM course c JOIN course_subject s ON c.course_id=s.course_id AND s.course_offer_number=1 AND s.subject'ME' AND c.catalog_number = 520 LEFT JOIN ( SELECT c.course_id,s.course_offer_number,s.subject FROM course c JOIN course_subject s ON c.course_id=s.course_id AND s.subject='ME' AND c.catalog_number = 520 ) m ON c.course_id=m.course_id WHERE m.course_id IS NULL I guess I suppose that there could be multiple 'ME' rows for a particular catalog_number, with different course_offer_numbers. But basically, I only want to grab one subject value. I want it to be 'ME' if a row exists with that subject. Otherwise, grab whatever subject the catalog_number has that's not equal to 'ME' and that has a course_offer_number of 1. Not really sure what to do if there are multiple. I guess for the time being, we are assuming that if there is no 'ME' row, then there will be only one other row for that catalog_number. H..I'm starting to think that what I'm asking for is not doable in one select statement. Thanks bunches. Tina Peter Brawley wrote, On 6/27/08 1:32 PM: What I want is ONLY the 'ME' row (if a row exists with a subject of 'ME'). If an 'ME' subject row does not exist, then I want the other one. Ill be offline for awhile so I'll assume answers not available, ie allow ='ME' dupes and 'ME' dupes if they exist. A one-query answer is to union (i) a query for 'me' matches and (ii) a query for 'non-me' matches which excludes 'me' matches: SELECT c.course_id,s.course_offer_number,s.subject FROM course_table c JOIN course_subject s ON c.course_id=s.course_id AND s.subject='ME' UNION SELECT c.course_id,s.course_offer_number,s.subject FROM course_table c JOIN course_subject s ON c.course_id=s.course_id AND s.course_offer_number=1 AND s.subject'ME' LEFT JOIN ( SELECT c.course_id,s.course_offer_number,s.subject FROM course_table c JOIN course_subject s ON c.course_id=s.course_id AND s.subject='ME' ) m ON c.course_id=m.course_id WHERE m.course_id IS NULL; PB -- *Tina Matter* Web Software Developer Department of Mechanical Engineering University of Michigan 734-763-3184 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query - One-to-Many question
I have two tables: 1.) A course table (stores course_id and catalog_number) 2.) A course_subject table (stores course_id, catalog_number, subject, and course_offer_number) For each row in the course_table, there can be many rows in the course_subject table, due to cross-postings among different departments. Here is what I'd like to do. I would like to grab a list of catalog_numbers from the course_table, but I only want one row in the course_subject table. Basically, if the subject is ME, then I want to select that row. If there is no row for that catalog_number that has a subject of ME, then I want to grab the row that has a course_offer_number of '1' and a subject that is not equal to ME. I am basically only grabbing the subject field from the course_subject table. If an ME subject exists for a catalog_number, grab that one. Otherwise, grab whatever other one exists.This is assuming that there will only be one other one. Does this make sense? I'd really like to know if there's a way to do this in one query. I can probably do it in PHP with multiple selects, building my list as I go. But if there's a fancy way to do this in one query, then I'd much rather do it that way. Thanks for any help. Tina -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query - One-to-Many question
Peter, That was the first query I tried, but for some reason, it still pulled all of the rows. So I've been trying to come up with another solution. Any other ideas? Thanks for the reply. Tina Peter Brawley wrote, On 6/26/08 2:12 PM: Tina Basically, if the subject is ME, then I want to select that row. If there is no row for that catalog_number that has a subject of ME, then I want to grab the row that has a course_offer_number of '1' and a subject that is not equal to ME. Is this what you mean? SELECT ... FROM course_table c JOIN course_subject s ON c.course_id=s.course_id WHERE s.subject=ME OR (s.course_offer_number=1 AND s.subject ME); PB Tina Matter wrote: I have two tables: 1.) A course table (stores course_id and catalog_number) 2.) A course_subject table (stores course_id, catalog_number, subject, and course_offer_number) For each row in the course_table, there can be many rows in the course_subject table, due to cross-postings among different departments. Here is what I'd like to do. I would like to grab a list of catalog_numbers from the course_table, but I only want one row in the course_subject table. Basically, if the subject is ME, then I want to select that row. If there is no row for that catalog_number that has a subject of ME, then I want to grab the row that has a course_offer_number of '1' and a subject that is not equal to ME. I am basically only grabbing the subject field from the course_subject table. If an ME subject exists for a catalog_number, grab that one. Otherwise, grab whatever other one exists.This is assuming that there will only be one other one. Does this make sense? I'd really like to know if there's a way to do this in one query. I can probably do it in PHP with multiple selects, building my list as I go. But if there's a fancy way to do this in one query, then I'd much rather do it that way. Thanks for any help. Tina No virus found in this incoming message. Checked by AVG. Version: 8.0.101 / Virus Database: 270.4.1/1521 - Release Date: 6/26/2008 11:20 AM -- *Tina Matter* Web Software Developer Department of Mechanical Engineering University of Michigan 734-763-3184 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query - One-to-Many question
Even if I do a basic select (with no joins) for a given catalog_number, I still get two rows back. Even if I do this simple query, while hardcoding in a catalog_number: SELECT subject, catalog_number FROM course_subject WHERE (catalog_number = 520) AND ((subject = 'ME') OR ((course_offer_number = 1) AND (subject NOT LIKE 'ME'))) I still get two rows back: subjectcatalog_number ME520 MSE520 So I'm not sure what else I need in my Where Clause Thanks bunches. Tina Peter Brawley wrote, On 6/26/08 3:33 PM: Tina, for some reason, it still pulled all of the rows Are there multiple rows which meet your WHERE condition? If so, and if you want just one of them, your need another WHERE condition. PB - Tina Matter wrote: Peter, That was the first query I tried, but for some reason, it still pulled all of the rows. So I've been trying to come up with another solution. Any other ideas? Thanks for the reply. Tina Peter Brawley wrote, On 6/26/08 2:12 PM: Tina Basically, if the subject is ME, then I want to select that row. If there is no row for that catalog_number that has a subject of ME, then I want to grab the row that has a course_offer_number of '1' and a subject that is not equal to ME. Is this what you mean? SELECT ... FROM course_table c JOIN course_subject s ON c.course_id=s.course_id WHERE s.subject=ME OR (s.course_offer_number=1 AND s.subject ME); PB Tina Matter wrote: I have two tables: 1.) A course table (stores course_id and catalog_number) 2.) A course_subject table (stores course_id, catalog_number, subject, and course_offer_number) For each row in the course_table, there can be many rows in the course_subject table, due to cross-postings among different departments. Here is what I'd like to do. I would like to grab a list of catalog_numbers from the course_table, but I only want one row in the course_subject table. Basically, if the subject is ME, then I want to select that row. If there is no row for that catalog_number that has a subject of ME, then I want to grab the row that has a course_offer_number of '1' and a subject that is not equal to ME. I am basically only grabbing the subject field from the course_subject table. If an ME subject exists for a catalog_number, grab that one. Otherwise, grab whatever other one exists.This is assuming that there will only be one other one. Does this make sense? I'd really like to know if there's a way to do this in one query. I can probably do it in PHP with multiple selects, building my list as I go. But if there's a fancy way to do this in one query, then I'd much rather do it that way. Thanks for any help. Tina No virus found in this incoming message. Checked by AVG. Version: 8.0.101 / Virus Database: 270.4.1/1521 - Release Date: 6/26/2008 11:20 AM No virus found in this incoming message. Checked by AVG. Version: 8.0.101 / Virus Database: 270.4.1/1521 - Release Date: 6/26/2008 11:20 AM -- *Tina Matter* Web Software Developer Department of Mechanical Engineering University of Michigan 734-763-3184 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
