Re: SELECT DISTINCT question
Sheryl, I'm trying to determine which author has the highest royalty percent FOR EACH PROGRAM, not overall. I'm displaying a list of programs and authors, and when there is more than one author, I want to show the principal author (i.e., the one earning the highest royalty percent). Ok, so your desired result will look like... what??? Program ID, Author, Royalty Amount 1, smith, $100 2, jones, $250 Right? Ok, so what should your SELECT statement look like...? Shouldn't be too hard to figure out... Also, I don't think you want SELECT DISTINCT * I think SELECT DISTINCT a.AUTHOR, r.ROYALTIES. etc. is better Also remember, a.AUTHOR will give you different results than r.AUTHOR.. How will the results differ? First of all, since you have tables, have you tried populating the tables with data. What did you get? I guess I'm not clear on how DISTINCT works. Think of DISTINCT as meaning unique... Or, another way of looking at it, it answers the question, what are the distinct (or unique) pieces of data in a column?. Will it only look at combinations of a.AUTHOR and r.ROYALTIES if I write it that way? Yes, if you write it that way... Assuming Table a contains this: Author -- Smith Jones Johnson Assuming Table r contains this: Author Royalties Smith $100 Johnson $100 Based on the logic I described above, how do you think the queries results will differ, depending upon if I used Table a or Table b in my DISTINCT statement? SELECT DISTINCT a.Author, will resulting in: Smith Jones Johnson SELECT DISTINCT r.Author, will resulting in: Smith Johnson but... no Jones, because Jones has no record in Table b. I will say it again, it answers the question, what are the distinct (or unique) pieces of data in a column?. I don't want all the authors in order of royalty percent. I want the them to be in groups by Program ID and ordered by royalty percent within that (or just take the max within each group). Which is it, grouped by Program Id and ordered by royalty percent Or Just the Max in each group. You need to make up your mind - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT DISTINCT question
Sheryl, Sorry I don't time to fully address your question but I will try to give you some feedback. The DISTINCT functionality will give you exact that, every distinct instance of the columns you SELECTED. SELECT DISTINCT a.Author FROM author a Will give you each and every author but only once. Since your Royalties table has pointers to both Authors and Programs, if you run DISTINCT *, you will get every single instance of every combination. Before you go any further, I would attempt to answer one question. Are you trying to determine which Author had the highest royalties or are you trying to determine which Author has the highest royalties in each program(I am assuming an author can work on multiple programs)? This will make a difference in how your structure your query. Also, I don't think you want SELECT DISTINCT * I think SELECT DISTINCT a.AUTHOR, r.ROYALTIES. etc. is better Also remember, a.AUTHOR will give you different results than r.AUTHOR.. Hope this helps. At 10:13 AM 2/27/03 -0500, Sheryl Canter wrote: Didn't receive answer to message below. Could someone please take a look? TIA. - Sheryl - Original Message - From: Sheryl Canter [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 11:49 AM Subject: SELECT DISTINCT question I need help with a query. I have a 'royalties' table that looks like this: AuthorIDProgramIDRoyalty -- Author1 Program1 0.15 Author2 Program1 0.10 Author3 Program2 0.25 Author4 Program3 0.05 Author5 Program3 0.20 The primary key of this table is a combination of AuthorID and Program ID. Author information is stored in a separate table: AuthorIDFirstNameLastName Author1 Joe Smith Author2 BrianJones Author3 Jeff Tucker Author4 MichaelMoore Author5 MarkMann The main page of my Web site has a program list that includes the program name and author name (and other information). I want it to show the author receiving the highest royalty amount. Right now I'm not considering the possibility that more than one author can work on a program (since currently none is), and my SELECT statement looks similar to this: SELECT * FROM programs p, authors a, royalties r WHERE p.ProgramID = r.ProgramID AND a.AuthorID = r.AuthorID I could change this to SELECT DISTINCT * ..., but then which author would I get? If it's always the first encountered row, then could I avoid checking the royalty by always inserting the authors into the table in the correct order? (I know this is sloppy.) What is the rule used by SELECT DISTINCT to choose which row to return? If I wanted to do it right and select the author receiving the maximum royalty, how would I adjust the SELECT statement? TIA, - Sheryl - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT DISTINCT question
Sheryl, See my comments below... (1) What is the rule that SELECT DISTINCT uses when deciding which of multiple instances to return? Does it return the first one? How it selects the distinct items is outside my area of knowledge. I would not assume it is the first one or the last one, or anywhere in between. If there are multiple records for one author in a table and you run a distinct, your results set will include one record for that one author, regardless of where he/she is in your table. (2) Is there a way to write a SELECT statement to return the record for the author with the highest royalty percent (a different field in the table)? That is the one that I want. I would try the MAX() function... see link... http://www.mysql.com/doc/en/Group_by_functions.html#IDX1359 If you use ORDER BY, it will give you all the authors in order (I don't think you want that, do you?). ... but you still need to answer the question that I posed before Good luck. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL DB structure for scrolling list
At 04:58 PM 2/17/03 +0100, you wrote: Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query,queries,smallint If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: I am in the process of designing a form and I am a little confused. I would like to have a scrolling window, in which the user can select multiple items. For example, the scrolling window may contain operating systems, and the user has the ability to select multiple OS's (using the Control-Click combination), to describe themselves. I am unsure how this data should be stored. I am using a MyISAM table, but I am unsure about the type of field this information should be stored in. And, when the individual wants to update the information, how it should be displayed. Any advice would be appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: need help with GROUP BY
Jaime, You should be using the DISTINCT function. SELECT DISTINCT account, id, FROM etc. This should give you one instant of 'account' in your SELECT output. At 09:24 AM 2/5/03 +, Jaime Teng wrote: I have a MySQL table: ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | account| varchar(10) | | MUL | || | detail | text | YES | | NULL|| ++--+--+-+-++ I would like to perform a search, SELECT id,account FROM tablename WHERE detail LIKE '%pattern%'; However, this would produce several hits for a single account. I'd like it to produce only one hit *per* account and give me id where '%pattern%' was found and account where it was found on. It should only return *one* result per account. regards, Jaime - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: need help with GROUP BY
Jaime, You should be using the DISTINCT function. SELECT DISTINCT account, id, FROM etc. This should give you one instant of 'account' in your SELECT output. At 09:24 AM 2/5/03 +, Jaime Teng wrote: I have a MySQL table: ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | account| varchar(10) | | MUL | || | detail | text | YES | | NULL|| ++--+--+-+-++ I would like to perform a search, SELECT id,account FROM tablename WHERE detail LIKE '%pattern%'; However, this would produce several hits for a single account. I'd like it to produce only one hit *per* account and give me id where '%pattern%' was found and account where it was found on. It should only return *one* result per account. regards, Jaime - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is this possible?
I believe a sub-select with the DISTINCT command would do it. You would have to look up the syntax. Also, it seems that some people seem to be having a problem with sub-selects but I don't have any direct experience to convey. At 01:12 PM 1/23/03 -0600, Doug Coning wrote: Is this possible in MySQL without using a temporary table with multiple SQL commands: I am trying to SELECT the results of multiple columns into 1 column or Alias, group them and sort them. So if I had a table with 4 fields: Cat1, Cat2, Cat3, Cat4 like such: +-+--+-+--+ | Cat1| Cat2 | Cat3| Cat4 | +-+--+-+--+ | F | A | | A | | D | C | H | A | | G | | | | | F | B | I | A | +-+--+-+--+ It would select all the values, merge them into 1 column, group them, and sort them like such so that the result would be a single column, returning only 1 instance of each returned value: ++ | Result | ++ | A | | B | | C | | D | | F | | G | | H | | I | ++ Is there as singe Select command that can do this? Right now I am using a temporary table where I first clear out the values, then copy each column one at a time over into the tables... It works, however, I was wondering if there is an easier way to get the single column result with all the merged information without having to use a temporary table... Thanks, Doug Coning - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Entering Japanese characters into mySQL
Darren, This sounds like a character set mis-match. I am wondering what character set the user is using and which OS. If the user is using Netscape to enter their data, then I am assuming they are using an OS that supports the hira/kata/kanji character sets. What do they have their OS set to, from a character set perspective? I think I would start there At 12:58 PM 1/23/03 +, Darren Luckett wrote: hello, I'm new to this mailing list and am having trouble with mySQL and japanese characters. I have a web form in Netscape that the user enters japanese text (hiraghani). the data is posted to a dbinsert.jsp page. the problem is that somewhere along the line the character set transforms into half-width katakana (usually a language used for non-japanese origin words - i.e English or Computer). I am using JSP and Tomcat with Apache on Mac OSX. I've tried setting the charset to Shift-JIS, JIS, ujis, AutoDetect EUC but with no results. JIS is the most stable. Can anybody help Thanks Darren Luckett E: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fwd: Re: Copying MySql database to others
Query. I guess I don't understand how this mailing list works.. I send messages and some of them get posted, some don't . Some instances, I send a message and it gets posted, I try to respond and the message never appears Can someone explain? Date: Wed, 08 Jan 2003 18:37:33 -0800 To: Stefan Hinz, iConnect \(Berlin\) [EMAIL PROTECTED], [EMAIL PROTECTED] From: Frank Peavy [EMAIL PROTECTED] Subject: Re: Copying MySql database to others Thanks everyone for your input. Stefan, From, within phpMyAdmin? snip 1. In the left frame, choose the database you want to backup / copy. 2. Click the EXPORT tab in the right frame. 3. Choose the tables in the database you want to backup. 4. Choose Structure and data. 5. Check Enclose table and field names with backquotes if your table or column names might contain special characters (like ä, ö, ü). 6. Check Save as file. 7. Click Go. Since I have limited command line access (ISP hosted database), I will probably have to use phpMyAdmin. Part of my logic was to copy my MySql database to another MySql database, so that I can use the second database for QA purposes. When I perform the Export as above, does that make a copy in another database or is that to an external file? If it is an external file, would I have to Import it to another database that I create? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Copying MySql database to others
Stefan, Let's say, you exported database my_database, and you saved the export file as c:\mysql\my_database_export.sql on your QA machine. You can import it with this command (assuming that database my_database exists on your QA machine, but has no tables in it): c:\mysql\bin mysql -uusername -ppassword my_database c:\mysql\my_database_export.sql Doesn't this assume that I have command line access? Since I am in a hosted (ISP) environment, I am trying to do this through phpMyAdmin. Can I not do this through phpMyAdmin? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Copying MySql database to others
Thanks everyone for your input. Stefan, From, within phpMyAdmin? snip 1. In the left frame, choose the database you want to backup / copy. 2. Click the EXPORT tab in the right frame. 3. Choose the tables in the database you want to backup. 4. Choose Structure and data. 5. Check Enclose table and field names with backquotes if your table or column names might contain special characters (like ä, ö, ü). 6. Check Save as file. 7. Click Go. Since I have limited command line access (ISP hosted database), I will probably have to use phpMyAdmin. Part of my logic was to copy my MySql database to another MySql database, so that I can use the second database for QA purposes. When I perform the Export as above, does that make a copy in another database or is that to an external file? If it is an external file, would I have to Import it to another database that I create? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Copying MySql database to others
I need to copy a MySql Production database to QA. What is the best method of doing this? Do I need to write SQL queries or is there another quicker method? From the command line? From, within phpMyAdmin? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Copying MySql database to others
Thanks everyone for your input. Stefan, From, within phpMyAdmin? snip 1. In the left frame, choose the database you want to backup / copy. 2. Click the EXPORT tab in the right frame. 3. Choose the tables in the database you want to backup. 4. Choose Structure and data. 5. Check Enclose table and field names with backquotes if your table or column names might contain special characters (like ä, ö, ü). 6. Check Save as file. 7. Click Go. Since I have limited command line access (ISP hosted database), I will probably have to use phpMyAdmin. Part of my logic was to copy my MySql database to another MySql database, so that I can use the second database for QA purposes. When I perform the Export as above, does that make a copy in another database or is that to an external file? If it is an external file, would I have to Import it to another database that I create? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multiple SQL files
I have multiple SQL files that create different tables. Is there a way for me to create a single SQL file that will call these other files? Since I am using phpMyAdmin, I am assuming that call this file from phpMyAdmin?! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: user's updates trace
Natale, It looks like you are looking for some kind of audit trail.. It would show all the changes to the table data, right? Sorry, I don't have an answer, but I am looking for some thing similar. If I find something that falls into this category, I will try to post. What I have seen in some companies using other databases is, they trap all the transactions in a single table but you need to build it in Need to keep looking there must be a better answer. At 02:54 PM 1/3/03 +0100, Natale Babbo wrote: # S.O.S. # hi all, anyone knows how to get the history of updates of a user? ... i mean ... in my database i have a lot of users with different privileges. what i need is to reach all the insert, update and delete queries executed by one user. is it possible? i check the bin logs but they seems not to trace the user that execute the query. is it true? thanks in advance. natale babbo P.S.: plase help me ... any tips are appreciated!! __ Yahoo! Cellulari: scarica i loghi e le suonerie per le tue feste! http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multiple SQL files
I have multiple SQL files that create different tables. Is there a way for me to create a single SQL file that will call these other files? Since I am using phpMyAdmin, I am assuming that call this file from phpMyAdmin?! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Multiple SQL files
Warren, Thank you for your prompt reply. Actually, I have various ??.sql files that I can execute thru phpMyAdmin and they all work, but on occasion, there is a need to run all the files at once. So, I was wondering if I could create a file the would call these other files and execute them in sequence, i.e. Aggregate_file.sql call first.sql call second.sql call third.sql etc. If you have an example of something like this, it would be nice to see. Thanks. At 11:48 AM 1/5/03 -0800, wcb wrote: Hi! I have php files and perl files that create multiple databases and move data around as needed. . . is this what you mean? You could have many such files if you wanted (as you mention below) but it would seem easier to set up some sort of program flow so that under different conditions different databases could be created or dropped or whatever automatically. . . If this is what you mean, then yes, it is being done even as we speak! I could send you one of my inept examples if you want. I believe that phpMyAdmin will allow you to enter SQL commands (say, by copying them from a file and pasting into the SQL code window). Unless you modified phpMyAdmin (which ought to be pretty easy) I think that you'd have to set up an external file to create multiple databases and do your bidding with the data. . . Cheers! -warren Filter: mysql, query, queries, bigint - Original Message - From: Frank Peavy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, January 05, 2003 11:39 AM Subject: Multiple SQL files I have multiple SQL files that create different tables. Is there a way for me to create a single SQL file that will call these other files? Since I am using phpMyAdmin, I am assuming that call this file from phpMyAdmin?! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Batch Queries
Anyone know how to run batch queries in phpMyAdmin? How is it done? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Search Engine (text search) like functionality...need to build
Hello All, I have a need to build some user functionality (text search) that is similar to the search functions available in most search engines. I would like to have users input a search string and find the appropriate records in MySql, for example: database software development linux Part of the difficulty I am having is how to handle the pieces of text to search with. A simple parse of the data would break apart the text inside the double quotes. My intent was to capture the search string in a PHP form, but I am unsure where to go to from there. Any thoughts on how to approach this would be appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: join help: i am lost
David, I am unsure if I followed your example completely, but maybe this might help. Not knowing your complete database structure, I am unsure if my comments will be entirely valid but here goes. I think you could achieve your goal if you think of your groups as containing one or many clients. Each single client would be in a group of their own. Yes, this is a little strange, but it makes the structure a lot easier and consistent. So this is what you would have: Time slot -- class -- group -- client So the structure, in english: Each time slot has a one-to-many relationship to classes Each class has a one-to-many relationship to groups Each group has a one-to-many relationship to clients Now, you can query the database and see how many time slots have more than one class. You no longer need to worry about double booking. Hope this helps At 11:23 AM 1/1/03 -0500, David T-G wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, all -- I need, I think, some pointers to basic JOIN tutorials. I don't really know how to approach this query. I should say early on that I don't expect the list to write my code for me, though any help anyone can send is VERY much appreciated; rather, I don't even know where to look to do the required reading :-) I have a schedule table that looks like create table schedule ( # ID number id smallint not null default 0 auto_increment primary key , class smallint not null , # references classtypes.id client smallint not null , # references client.id instr smallint not null , # references personnel.id place smallint not null , # references places.id timeslot datetime not null ,# when #unique (timeslot,client) , # no double-bookings #unique (timeslot,instr) , # no double-bookings index (timeslot,client) , # no double-bookings index (timeslot,instr) ,# no double-bookings index (timeslot,place) ,# cannot be unique 'cuz of group classes cancelled datetime not null # cancelled? when? ) ; that holds my bookings. Classes can be either private (one client) or group (some number N, though perhaps only 1 client will sign up). I started out, as you can see, with unique indexes for the client and instructor, but since I had the brilliant idea of creating some N rows for a group class, all with empty client fields, that doesn't work. I'm not so worried about that; it just means that I'm going to have to do some work on my own to ensure no double-bookings (except for a group class). [OK, so maybe it wasn't that brilliant; better approaches will be heard with avid interest. But it worked in my *head*! :-] Then I had the idea of using client id '0', which will never occur in the client table (create table clients ( id smallint unsigned not null default 0 auto_increment primary key , ...);), as a way to black out a time slot so that the instructor can be guaranteed a lunch break or so. That's where my problem really came up. Thinking at first only of an instructor and single clients I came up with (deep breath) select substring(s.timeslot,1,13) , concat(c.fname,' ',c.lname) from personnel as i , clients as c , schedule as s where i.id = s.instr and c.id = s.client and i.fname = 'penelope' order by timeslot; which gives me a lovely ++-+ | substring(s.timeslot,1,13) | concat(c.fname,' ',c.lname) | ++-+ | 2002-12-27 06 | david t-g | | 2002-12-27 07 | david t-g | | 2002-12-27 10 | david t-g | | 2002-12-27 11 | harmon | | 2002-12-27 13 | larry thorburn | ++-+ and I write my table in php without a second thought. Then, however, comes the mess of pulling out any records where the client id is 0; for every timeslot like that, I get a row for each client in the clients table! Here's where I'm really swamped. I can write a separate query for schedule records where the client is 0, and I can write a separate query for schedule records where the class is not private, but how can I combine all three to get one lovely result to use to build my table? TIA HAND Happy New Year mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+ExYNGb7uCXufRwARAp0EAJ9a5C3emiflZGtuiXPOcWnMJo7iXQCeKVMw 0w2kNXILUltbWs/rxUwG22E= =kYIn -END PGP SIGNATURE- - Before posting, please
Re: join help: i am lost
David, Just some thoughts.. See my comments below... A scheduling, or a booking, eventually has to have a class type (private or one of many groups -- so I suppose I could simply make a group class type 'private' and that type has only one slot), an instructor, a place, a time slot, and the client or clients to go in it. Sounds like you are on the right track. Looking at your first sentence below, you state that there are various types of groups classes, so why not have a type of 'private'. If I get you right, I'd have a class table pulling together the type of class (one of the typical group classes or this new 'private' one) and, somehow, the client(s) enrolled, and then the schedule table need only have the class instantiation (which doesn't yet make sense without a timestamp; This is unclear, what do you mean by timestamp...do you mean it has no time scheduled? From a technical standpoint, this is not an issue, but from a business standpoint, you would have people enrolled in a class that has not been scheduled. (Can happen but awkward...) First, I wonder if I successfully followed you :-) Second, though, I don't get how I can have some clients in a class table when the class hasn't been assigned a time slot; how can the clients avoid collisions? This is unclear collisions?... Are you asking how the clients would avoid double booking themselves? If that is the question, I think your business process has to control that i.e. you need to schedule class timeslots. Or, as the classes get scheduled, you would have to notify your clients about the schedule. Wouldn't you have to do that any way, since they would not know when the classes are supposed to be? I % Now, you can query the database and see how many time slots have more than % one class. % You no longer need to worry about double booking. Because I can come back to an unique index, you mean, perhaps? You run a query that counts the number of classes that are booked for each location at each timeslot and if the count is greater than 1, you have a problem. (simple SQL query would give you this) Just to re-itereate, I think you want to enroll groups..! not clients..! A client can be a group of one. This allows you to enroll groups of one or many. I am assuming that your clients may be, for examplle, John Smith or local community center(?) Also, if it was me, I would probably create a table for time slots. The way you have it structured, it works but it is not as flexible. Your finally scheduling table would have pointers to places, timeslots, personnel, groups, classes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: join help: i am lost
Even so, that still doesn't answer the question of how to have data of different magnitude in the same table. If I have one class with one person and another with two people, how would I have a single record for each which lists the client(s)? Easy, Your scheduling query results, as I said: places, timeslots, personnel, groups, classes 5th floor room, 1 to 2, BestInstructor, Local community center, yoga (group lesson) 5th floor room, 2 to 3, BestInstructor, Jon Smith group, yoga (private) 5th floor room, 5 to 6, BestInstructor, Open Group, yoga (open) In order to find out if Jon is scheduled twice, you would need to know if he is part of the Local community center group. Any way You may have to re-think parts of your database structure. A good book on ERD diagrams might help. Best of luck. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Pattern Match on 3.23
Qunfeng, Thanks for the feedback, I surely appreciate it. I asked the pattern match question, because I am using a hosting service that hosts MySQL 3.23. Since I have a need to search on terms less than 3 characters long and I can not re-compile, I was looking for another solution. I thought that I might be able to use pattern matching as a substitute, but it sounds like performance might be an issue with large tables. If you have any other recommendations on how I could approach my problem, I would surely appreciate them. At 07:35 AM 12/31/02 -0800, Qunfeng Dong wrote: It can perform pattern match on text field. The only draw back is the speed (especially if you are using %pattern% to do the search) when you tables are getting huge, since there is no index to help. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: I would like to use pattern matching as a substitute to fulltext search on MySQL 3.23. Is this a good alternative? Are there any limits, like not being able to perform a pattern match on a 'text' field, etc., that I need to be aware of? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Pattern Match on 3.23
Qunfeng, ..millions of records.. seems like a lot... Would you be kind enough to provide me with your hardware configuration? Thanks. At 07:57 AM 12/31/02 -0800, Qunfeng Dong wrote: If you are searching with %pattern%, your speed is depending on the speed of table-scan. The speed of tablescan depends on your my.cnf setting (increase record buffer size?) and how big your records are. I am using pattern search here with millions of records and the performance is not terriblly too bad. Maybe you can still use fulltext search for general cases; and use pattern match ONLY when you are searching for 3-char-term. You should be able to make such Switch through your interface. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: Qunfeng, Thanks for the feedback, I surely appreciate it. I asked the pattern match question, because I am using a hosting service that hosts MySQL 3.23. Since I have a need to search on terms less than 3 characters long and I can not re-compile, I was looking for another solution. I thought that I might be able to use pattern matching as a substitute, but it sounds like performance might be an issue with large tables. If you have any other recommendations on how I could approach my problem, I would surely appreciate them. At 07:35 AM 12/31/02 -0800, Qunfeng Dong wrote: It can perform pattern match on text field. The only draw back is the speed (especially if you are using %pattern% to do the search) when you tables are getting huge, since there is no index to help. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: I would like to use pattern matching as a substitute to fulltext search on MySQL 3.23. Is this a good alternative? Are there any limits, like not being able to perform a pattern match on a 'text' field, etc., that I need to be aware of? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Pattern Match on 3.23
Thanks Qunfeng, Hope you have a Happy New Year.! At 11:17 AM 12/31/02 -0800, Qunfeng Dong wrote: We are running on linux redhat 7.3, RAM 4G, I am using my.huge-cnf. 1.2Ghz dual CPUs PIII. The mySQL database resides on SCSI disk. We do pattern search on varchar or text fields of a table with about 2.6 millions records and going (it also joins with other smaller tables). Hope this helps. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: Qunfeng, ..millions of records.. seems like a lot... Would you be kind enough to provide me with your hardware configuration? Thanks. At 07:57 AM 12/31/02 -0800, Qunfeng Dong wrote: If you are searching with %pattern%, your speed is depending on the speed of table-scan. The speed of tablescan depends on your my.cnf setting (increase record buffer size?) and how big your records are. I am using pattern search here with millions of records and the performance is not terriblly too bad. Maybe you can still use fulltext search for general cases; and use pattern match ONLY when you are searching for 3-char-term. You should be able to make such Switch through your interface. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: Qunfeng, Thanks for the feedback, I surely appreciate it. I asked the pattern match question, because I am using a hosting service that hosts MySQL 3.23. Since I have a need to search on terms less than 3 characters long and I can not re-compile, I was looking for another solution. I thought that I might be able to use pattern matching as a substitute, but it sounds like performance might be an issue with large tables. If you have any other recommendations on how I could approach my problem, I would surely appreciate them. At 07:35 AM 12/31/02 -0800, Qunfeng Dong wrote: It can perform pattern match on text field. The only draw back is the speed (especially if you are using %pattern% to do the search) when you tables are getting huge, since there is no index to help. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: I would like to use pattern matching as a substitute to fulltext search on MySQL 3.23. Is this a good alternative? Are there any limits, like not being able to perform a pattern match on a 'text' field, etc., that I need to be aware of? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: simple (I thought) delete question
I guess I am a bit confused at the response below... Please see: http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html Why can't the foreign key logic be used in version 3.23?.. At 09:45 AM 1/1/03 +1100, Greg Matthews wrote: Didn't fully read your question. Need the extra bit in the subselect to identify air force rows only, but again, this won't work until 4.1 with subselects is released. delete from tids where exists ( select 1 from tids_admin where tids_admin.id = tids.admin_id and tids_admin.service = 'Air Force') - Original Message - From: Greg Matthews [EMAIL PROTECTED] To: Richard Forgo [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, January 01, 2003 9:29 AM Subject: Re: simple (I thought) delete question if you want to do it in one statement, you'll need to wait for subselects to be implemented in MySql. Apparently this is underway and was scheduled to appear in MySql 4.1 if subselects were supported, you'd do something like this: delete from tids where exists ( select 1 from tids_admin where tids_admin.id = tids.admin_id) otherwise, you'll need to issue multiple statements. e.g. 1. locate records to delete 2. delete records either in bulk using an IN (id1, id2, id3,) expression or just deleting one at a time greg. - Original Message - From: Richard Forgo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 01, 2003 8:12 AM Subject: simple (I thought) delete question Hi folks, I hope that dumb questions are allowed in here ... I have two MySQL tables and I would like to delete records from one based on associated values in another. For example, using the example tables below, how would I delete all the records in the TIDS table that had an associative value of 'Air Force' in the TID_ADMIN table? TID_ADMIN ID (pk) | Service 1 Army 2 Navy 3 Air Force TIDS -- ID | ADMIN_ID | Project Name -- 1 1 Project X 2 1 Project Y 3 2 Vision C 4 3 Clearout T 5 3 Clearout F 6 3 Trustee 433 I have tried, to no avail, using variants of ... DELETE FROM tids, tid_admin WHEREtid_admin.admin_id = tids.admin_id AND tid_admin.service = 'Air Force' Can someone point me in the right direction. I'm still feeling my way through all this. Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Pattern Match on 3.23
I would like to use pattern matching as a substitute to fulltext search on MySQL 3.23. Is this a good alternative? Are there any limits, like not being able to perform a pattern match on a 'text' field, etc., that I need to be aware of? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Fulltext search of words 3 chars in 3.23
Any one else with any suggestions? Remember, re-compile is out of the
question, it's a hosted site.
Thanks.
At 10:49 PM 12/28/02 -0700, Mike Hillyer wrote:
He wants to execute a FULLTEXT search as opposed to a simple LIKE statement,
so I think REGEXP is out of the question.
Mike Hillyer
-Original Message-
From: JamesD [mailto:[EMAIL PROTECTED]]
Sent: Saturday, December 28, 2002 10:48 PM
To: Frank Peavy; [EMAIL PROTECTED]
Subject: RE: Fulltext search of words 3 chars in 3.23
You need to use the REGEXP capability instead of Like in a where clause
select 'field(s)' from 'table' where 'field' REGEXP '^[a-z]{1,3}$';
Jim
-Original Message-
From: Frank Peavy [mailto:[EMAIL PROTECTED]]
Sent: Saturday, December 28, 2002 6:55 PM
To: [EMAIL PROTECTED]
Subject: Fulltext search of words 3 chars in 3.23
Does anyone have a method of performing fulltext searches on words less
than 3 characters on MySql 3.23? I am dealing with a web hosting company so
a re-compile is out of the question.
Anyone have any good suggestions? I need to perform searches on acronyms
like php.
Thanks.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fulltext search of words 3 chars in 3.23
Does anyone have a method of performing fulltext searches on words less than 3 characters on MySql 3.23? I am dealing with a web hosting company so a re-compile is out of the question. Anyone have any good suggestions? I need to perform searches on acronyms like php. Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
