Re: Does Null == ?
On Thu, Sep 18, 2003 at 12:44:10PM +0100, Haydies wrote: I have only one thing to say really on this. Data never has any meaning at all. It is simply data, and not information. Information is extracted from data and is then given meaning by the viewer of the data. True. We were arguing about how to deal with meanings assigned by users. The words meaning and definition occur frequently in the thread. In the case of NULL I have always thought of it is simply undefined. Thats what Orecal says it is, and Paradox, Interbase, informix, and the if I type the list of databases I've used where that was the general idea it will go on and on and on all day long :-) It's a good definition, but it doesn't tell database designers when to use NULL. Unknown and Inapplicable give some guidance, whatever other problems they cause. As an aside, it was a sad day when codd died. Michael Stonebreaker may be the Codd of the current generation, but Edward was the Codd of our fathers. ducking and running Bob Hall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Null == ?
. There should be a boolean column for credit limit existence (HasCreditLimit Boolean) and a currency column for the amount of the credit limit. This mistake is so consistent that I think of it as diagnostic; if someone is trying to create two types of NULLs, they're probably violating 1NF. Usually this happens when a database needs to store a new attribute that the original designer didn't anticipate, and someone gets clever and tries to create special flags for existing columns instead of creating new columns for new attributes. My disagreement with Bruce Feist is not over whether NULL is ambiguous; the definition that database designers use is explicitly ambiguous. We disagree over how to deal with the ambiguity. My contention is that it is rarely a problem in a well designed database. But I acknowledge that it sometimes may be a problem. Bruce advocates restricting the meaning of NULL and documenting the restriction. My problem with that is that the RDBMS will continue to accept NULLs and process them properly, even when they violate the restriction. The meaning of NULL is well known, and is specified in the SQL standard in one form or another, so a DBA is fully justified in assuming that NULL is used according to the standard in any given database, and has no reason to look for documentation that tells him otherwise. NULL is any data that should cause any comparison to return UNKNOWN. This could be either unknown data or inapplicable data. This is akin to using a char or varchar column for dates. If you put a date into a DATE column in an invalid format, the column will spit the date back and complain. If you put an invalid date into a text column, the column will happily store it for you and all your aggregate queries will return inaccurate results. Similarly, if you try to use NULL for data that is applicable but unknown, the column will also accept NULL in cases where the data is inapplicable, and your assumptions about the meaning of NULL in that column will be wrong. The RDBMS will return FALSE on all comparisons with NULL, so there will be nothing to tell you that the column contains invalid NULLs. My experience is that if it is possible to put invalid data in a column, someone will do it. (I've done data cleanup on a subset of the payroll database for a multinational company. Lots of people had lots of opportunities to screw up the data, so they did.) When I've needed to specify that data in a text column is unknown, I've used Unknown or Not Known. This has two advantages. First, it doesn't redefine an established term. (NULL is defined as anything that should cause a comparison to return UNKNOWN, which includes *both* unknown and inapplicable data. This definition is widely known and accepted, and it is implemented in the RDBMS. Changing the definition for a specific database or table is dangerous.) The other advantage is that it is self-documenting. Anyone seeing Unknown or Inapplicable will know immediately that one doesn't mean the other, or both. Numeric columns are a bigger problem. You can put a number or a NULL into and INT column, but not Unknown or Inapplicable. In that case, the best solution is to allow NULLs, but add a boolean column called Unknown, with the restriction that both columns can't be NULL in the same row. So if the INT column contains a NULL, you have to specify what the NULL means in the Unknown column; 1 = unknown, 0 = inapplicable. If the column only contains non-negative integers, then you can assign -1 and -2 the meanings Unknown and Inapplicable. But make sure you disallow NULLs, or NULL will end up being used for both (as it should be if it is allowed). But this is coming dangerously close to cleverness, which is a bad quality in a database designer. These problems should occur very rarely. I'll repeat that if you are trying to split NULL into its two meanings, you are probably making a design mistake, and should add columns to capture additional data, rather than shoehorn multiple attributes into one column. If you have to create multiple NULLs more than twice in your career, reexamine your designs. Bob Hall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Null == ?
On Wed, Sep 17, 2003 at 09:58:16PM -0400, Bruce Feist wrote: Bob Hall wrote: Bruce Feist has initiated a discussion with me off the list, Off the list by accident, by the way. I sometimes forget that in this list I need to do a REPLY ALL. I generally don't go private unless I want to avoid embarassing someone or need to discuss something genuinely of no interest to others in the group; in this case, the conversation was of general interest and reasonably civil g. The conventional definition of NULL, whether or not it is included in the SQL standard, is Not Known or Not Applicable. This is both precise and ambiguous: Ambiguous because it has two possible meanings, and precise because it has only those two meanings. I'll agree with that. an unambiguous definition of NULL, found in the SQL standard, is the value that always causes a comparison operator to return FALSE in any known DBMS. Interesting approach. I haven't thought it through completely enough to decide whether or not I can agree that this is an unambiguous definition. For instance, I'm not sure what SQL is supposed to evaluate 0/0 as -- is it NULL, or does it invalidate the statement computing it, or is it some non-NULL null-like thing? (Rhetorical question; I don't necessarily expect an answer, although one would be nice.) RDBMSs seem to be roughly evenly divided between those that return NULL and those that return an error. This leads me to believe that the SQL standard doesn't say what to do when 0/0 is attempted. I do *not* think that it's a useful definition, though, because of the weakness you point out. It doesn't tell the designer how to use the feature. I cannot think of any case where it is useful to store a NULL if you don't know what it means other than that it's a value which when compared to other things returns UNKNOWN! In many ways, I see this as analagous to identifying the units that a numeric field is identified in; the field won't tell you whether it's in fortnights or seconds, so the documentation must. Like it or not, it is the definition that the RDBMS uses, so it is the definition that your database is using, whether you are aware of it or not. the distinction between unknown and inapplicable can be important, in theory. a bank may keep a record of credit limits for its customers. A database designer may be tempted to set aside a special number, say -1, to place in the credit limit column for customers who have no credit limit. the attribute of having a credit limit is different information from the attribute of the credit limit size. These two types of data should not be in the same column. There should be a boolean column for credit limit existence (HasCreditLimit Boolean) and a currency column for the amount of the credit limit. If you don't mind a brief red herring, consider the fact that not all RDBMSs support CHECK constraints, and in those which don't this approach gives rise to the possibility of inconsistent data: what happens if HasCreditLimit is FALSE and CreditLimit is $1? If people can enter data that will break your database, they will, so that's not really a red herring. If you can't use a CHECK constraint, then you need to run periodic queries that check for data consistency. It's a red herring only in the sense that we already knew that. It's a red herring, of course, because we're letting the real world intrude upon our theoretical discussion. In any real DBMS, experienced DBDs know that there are compromises which must be made; these don't invalidate what we'd *like* to do, but sometimes are things that we must do to have a well-functioning, or at least usable, system. Lack of a CHECK constraint in the target DBMS doesn't impact what the correct logical design is. It's not really a theoretical discussion. We're discussing how the SQL standard is implemented in existing DBMSs, and how that affects database design. This mistake is so consistent that I think of it as diagnostic; if someone is trying to create two types of NULLs, I wasn't suggesting creating two types of NULLs... I was suggesting that if NULLs are permitted for a column, the DBD should indicate which meaning of NULL is in use for that column. Only one meaning is ever permitted; the meaning hardcoded into the RDBMS. Anything that conforms to that meaning is always permitted. You have no way of altering that. The ambiguous definition used by designers is only an English language approximation. It doesn't matter whether the ambiguous definition is in the SQL standard or not because it is not implemented in the RDBMS. If your design permits only Unknown or Inapplicable, and you use NULL to represent this, you are reintroducing confusion that the SQL standard removed. If you remember that the ambiguous definition closely approximates the actual definition, then you will avoid
Re: Re Does NULL == ?
On Tue, Sep 16, 2003 at 09:58:32AM -0400, Randy Chrismon wrote: All this discussion about the definition of NULL and its use in database querying has been most interesting and enlightening. I hadn't realized I was asking such a deep question. Unfortunately, I'm still at the stage of designing tables where I KNOW I will be loading thousands of records that, more often than not, contain anywhere from 10% to 50% columns where no values have been entered for whatever reason (exporting an existing Lotus Notes database to MySQL). Naturally, that 10-50% will never consist of precisely the same columns from one record to the next. Putting aside issues about the distinctions among NULL, 0, and , my question is this: At the end of the day, which system is going to be easier to export to and which will be faster (produce result sets faster) when all the data are loaded? There are three formats for the DDL that I can think of immediately (I'm a newbie at this stuff): Use NULL. Disallow NULL only in columns where you know there will always be data. In the table you describe above, only the primary key will be certain to contain data. Bob Hall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Null == ?
On Tue, Sep 16, 2003 at 05:39:35PM -0400, Bruce Feist wrote: 1) It is the responsibility of the DBD to document the meaning of NULL if NULLs are allowed. No, it's not. The meaning of NULL is documented in the SQL specification. It's the responsibility of people working with databases to know what that definition is. If you need to specify one of the meanings of NULL, then you should use something other than NULL. Since NULL has two meanings, redefining it to have only one will create unnecessary confusion. Anyone working on or with the database will rightly assume that it has the standard meaning defined in the the SQL standard. Any problems caused by the confusion are the fault of the person who altered the meaning. If you want to indicate something other than the two standard meanings, then you should use something other than NULL. Otherwise, you create unnecessary confusion. Bob Hall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Null == ?
On Mon, Sep 15, 2003 at 12:46:50PM -0400, Randy Chrismon wrote: an exort from a Lotus Notes database. At some point, the MySQL documentation says that a table with no nullable columns is better/faster than one with. The Lotus Notes database I'm migrating, however, has many fields with no values. I infer from the MySQL documentation that I'm better off doing: create table my_table(a_field varchar(16) NOT NULL default , ...) and exporting my values from Notes as rather than using NULL. No. The meaning of NULL is defined in the SQL specification; it means not known or not applicable. If you have data that is missing because it is not known or not applicable, then use NULL. A zero-length string has no defined meaning. I've done maintenance on databases that contained zero-length strings, and they were nightmares. I can't think of any reason why you would use a zero-length string in a database. Bob Hall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Null == ?
On Mon, Sep 15, 2003 at 09:53:11PM -0400, Bruce Feist wrote: Bob Hall wrote: The meaning of NULL is defined in the SQL specification; it means not known or not applicable. Which is just about as useful as not defining it, actually. The That's not true. Having a univerally understood designation for not known or not applicable is extremely useful. It would have been better if the original SQL committee had specified a designation for each, but even the combined designation is useful. vagueness is the cause of a great many program bugs when database designers don't specify what NULL means for a given field. To give a hypothetical example: This is a really bad example. The application is payroll/personnel. A programmer is tasked with creating forms for data entry on new employees, including supervisor. If the user doesn't enter a new employee's supervisor, the application accepts it, figuring that it is not yet known, and stores NULL for the field (not known use of NULL). I've worked with payroll databases. Usually, the business rules forbid putting the employee into the database if certain data is missing. The employee literally doesn't get a paycheck until this data has been supplied. In other cases, new employees go into a preliminary table until the missing information is supplied. Meanwhile, a payroll programmer has been tasked with writing an application to give the CEO a huge bonus and stock options. To figure out which employee is the CEO, the application looks for the employee with NULL for supervisor (not applicable use of NULL). This database would be impossible to maintain if the company were more than a few hundred employees. A better solution is to assign the employee to a section and assign a supervisor to the section. That way, you don't have to change each employee's record each time the supervisor changes. Suddenly, a large number of new hires are fabulously wealthy. Who screwed up? Answer: the DB designer who didn't specify what NULL meant. The meaning of NULL is already specified. The fault lies in the design of the database, not the definition of NULL. If the database were designed this way, then the people updating it would have to take responsibility for ensuring that a change that applied to only one person wasn't accidentally applied to someone else. Which makes it even clearer that the database is badly designed. A zero-length string has no defined meaning. I've done maintenance on databases that contained zero-length strings, and they were nightmares. I can't think of any reason why you would use a zero-length string in a database. Because you know that a given person has no middle name? To represent no value, as differentiated from not known? If a person has no middle name, then the middle name field is not applicable, i.e. NULL. If an attribute is known to have no value, then you can't apply it to the entity. Bob Hall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Null == ?
On Mon, Sep 15, 2003 at 09:24:50PM -0500, Dan Nelson wrote: In the last episode (Sep 15), Bruce Feist said: Your example has nothing to do with the vagueness of NULL though. Replace NULL with 0 and you get the same result. Bad move. 0 has a universally known meaning, and it isn't not applicable. Doing this will cause a lot of confusion. Bob Hall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Null == ?
On Mon, Sep 15, 2003 at 10:33:27PM -0400, Bruce Feist wrote: if you stick to the natural meaning, that doesn't happen; integer and other values have precise and obvious natural meanings. NULL does not. Integers and NULL are exactly alike it this regard. Neither has a natural meaning; their meanings are complete arbitrary. There's nothing about the symbol 6 that makes it the natural designation for six. You wouldn't know that it stood for six unless you had been taught. What makes integers and NULL useful is that their meanings are conventional. The problem with a zero-length string is that it has no conventional meaning. Bob Hall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Null == ?
On Mon, Sep 15, 2003 at 07:29:38PM -0700, Jon Frisby wrote: That's an ugly way to make the distinction between A-mark and I-mark. In most situations, I'd move the relevant column(s) to a separate table, with a NULL-allowed column in that table and a FK reference back to the original table. The absence of a row in this child table indicates I-mark, and the presence of a row with a NULL in the column indicates an A-mark. Or alternatively you could just get a database that has two kinds of NULLs, specifically defined to represent the distinction you bring up. ( http://www.firstsql.com ) When the original ANSI committee was setting up the SQL specification, there were people who wanted to have two NULLs, one for not known and one for not applicable. The committee decided that this was too complicated, which I think was a mistake, but the unified NULL has proven pretty useful. It does, however, require you to understand its meaning and think about its use. Instead of restructuring the tables, if the column were a text type, I would disallow NULLs and specify the use of Not Known and Not Applicable in the documentation. Of course, this won't work for numeric columns. But for text columns, it has the advantage of being self-documenting. You can set the default value to Not Known, and the client app will have to supply Not Applicable where applicable. Generally, I've found that the following rules tend to eliminate problems associated with the unified NULL: 1) If an attribute is essential, then don't allow NULLs in the column. The data has to be supplied before a record can be created. 2) If you have to store incomplete records (i.e. missing essential data), then put them in a seperate table, which is known to contain incomplete records. You'll need some mechanism for checking the records and transferring them to the main database when they are complete. 3) If the record must go into the main database without the data, then the data isn't essential. If the data isn't essential, then the NULL is usually OK. For unessential attributes, it rarely matters whether the data is not known or not applicable. Bob Hall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: converting from MS Access
On Fri, Sep 12, 2003 at 05:02:29PM +, [EMAIL PROTECTED] wrote: Hi I am trying to convert an existing access database to MYSQl and not having much luck I have tried dbtools but I end up with nothing transfering. Am I doing something wrong or is there a better conversion tool You can export Access tables as tab delimited files, and import them into MySQL with mysqlimport or LOAD DATA INFILE. Check the documentation for details. This question comes up a lot, so you can get a lot of information by searching the archives. As to whether you're doing something wrong with dbtools; no one can answer your question without knowing what you are doing. Bob Hall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: don't work
On Fri, Sep 12, 2003 at 10:28:11PM -0400, SWIT wrote: Nice subject line huh ? Mysql 4.0.14 freebsd. (why does it say unknown by the way ?) I get this when trying to run badboy# Starting mysqld daemon with databases from /usr/local/mysql/data 030912 22:08:06 mysqld ended [1]Done ./mysqld_safe I RTFM twice. I followed the manual as best as I could. Now I was logged in as user su - when I did all the stuff that the instro's said to do. Mysql is installed in /usr/local/yada-yadaa-yada-whatever and I did the ln -s thingy (can u tell I'm not unix geek yet) I did all the chmods and chgrps per the manual. I can't find a error log either. not in /var/db not in /usr/local/mysql or the sub dir Installed from the binaries for freebsd. Am running version 4.8 I've always installed MySQL from the ports, so I never had to do those Unix thingies. Never had a problem. The simplest solution is to install from the ports. Bob Hall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Q: Concatenate multiple rows on same column.
On Thu, Aug 21, 2003 at 03:30:11PM +, Nick Heppleston wrote: I have a concatenation problem and I was wondering if somebody might be able to offer some help :-) Hi Nick, Your problem is a formatting problem, not a concatenation problem; i.e. SQL concatenation wasn't intended to solve this problem. SQL has very limited formatting capabilities. You need to send the SQL output to an application that will format it for you and generate a report. You can write the app yourself (PHP, Pearl, C, etc, etc) or you can use an existing app with report-formatting capabilities. Bob Hall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I wonder why nobody answered me
On Tue, Jun 17, 2003 at 07:50:08PM +0300, Primaria Falticeni wrote: Why nobody answered me? I didn't see your post. Very few people have the time to read everthing posted to this list. 2. How could I make accumulated sums in MySQL (described in the actual forwarded message) - Original Message - From: Primaria Falticeni [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 17, 2003 5:48 PM Subject: How the Partial SUMs are made in MySQL Hello, I need one complex query which makes partial sums on some dates. Let's get the structure of table rates: Day Date, Amount. 01/05/20033,000 01/06/20032,000 01/09/20034,000 SELECT Day as DayF,SUM(Amount) as S_Am FROM rates GROUP BY Day gives the list of totals each one from a day: DayFS_Am 01/05/20033,000 01/06/20032,000 01/09/20034,000 I need the sum of the Amount from the beginning of the file until each day...something like SUM(Amount FOR DayDayF) on each day. DayFS_Am 01/05/20033,000 01/06/20035,000 (I mean 3,000 on 01/05/2003 + 2,000 on 01/06/2003) 01/09/20039,000 (I mean 4,000 on 01/09/2003 + 3,000 on 01/05/2003 + 2,000 on 01/06/2003) In English, these are usually (but not always) called running totals, or running sums. SELECT r0.Day, SUM(r1.Amount) as S_Am FROM rates AS r0, rates AS r1 WHERE r1.Day = r0.Day GROUP BY r0.Day This runs fine in vi, but I haven't tried it in MySQL. Some adjustment may be necessary. If you're going to do much of this, I suggest getting a copy of SQL For Smarties by Joe Celko, which is where I learned how to do running totals. Bob Hall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Formatting SQL in Access
query database On Fri, Feb 14, 2003 at 11:17:22AM +1100, Daniel Kasak wrote: You have to use the visual basic constant: vbCrLf Or vbNewLine. Bob Hall - 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: converting MS SQL to MySQL
On Thu, Feb 06, 2003 at 10:03:18AM -0500, Hawk wrote: Hi, I am new to SQL. I am attempting to create a database that was created in MS SQL to MySQL. The below SQL statements works with MS SQL. MS SQL could refer to either Jet SQL or SQL Server SQL, but the SQL below is obviously SQL Server. So the database was created with SQL Server. The GO statements are not SQL. They are used by SQL Server utilities to mark the end of a batch. I don't think they're used by anything other than MS SQL Server utilities and the equivalent Sybase utilites. But regardless, GO isn't used by any MySQL utilities. Every DBMS uses its own dialect of SQL. When you switch from one DBMS (SQL Server) to another (MySQL), you will have to look up the syntax of the statements that don't run and learn how to rewrite them in the new SQL dialect. The best place to start looking for info is the on-line manual at www.mysql.com. Good luck, and have fun. Bob Hall I am executing the following: CREATE TABLE 'Accounting'( 'Username' VARCHAR(254) NULL, 'CallerID' VARCHAR(128) NULL, 'Addr' VARCHAR(128) NULL, 'NAS' VARCHAR(128) NULL, 'Port' VARCHAR(128) NULL, 'Start' DATETIME NULL, 'Stop' DATETIME NULL, 'SessionTime' INTEGER DEFAULT 0, 'ExtraTime' INTEGER DEFAULT 0, 'TimeLeft' INTEGER DEFAULT 0, 'KBytesIn' INTEGER DEFAULT 0, 'KBytesOut' INTEGER DEFAULT 0, 'SessionKB' INTEGER DEFAULT 0, 'ExtraKB' INTEGER DEFAULT 0, 'KBytesLeft' INTEGER DEFAULT 0, ) GO CREATE INDEX IX_Username ON Accounting(Username) MySQL will not take this command GO CREATE TABLE 'ActiveUsers'( 'UserID' VARCHAR(254) PRIMARY KEY, 'NAS' VARCHAR(128) NULL, 'Port' VARCHAR(128) NULL, 'Username' VARCHAR(254) NULL, 'CallerID' VARCHAR(128) NULL, 'Address' VARCHAR(128) NULL, 'LoginTime' DATETIME NULL, ) GO CREATE TABLE 'TAC_GRP'( 'TAC_ID' VARCHAR(254) NOT NULL, 'TAC_Attr' VARCHAR(64) NOT NULL, 'TAC_Val' TEXT NULL ) GO CREATE UNIQUE INDEX IX_TAC_ID ON TAC_GRP(TAC_ID, TAC_Attr) MySQL will not take this command GO CREATE TABLE 'TAC_USR'( 'TAC_ID' VARCHAR(254) NOT NULL, 'TAC_Attr' VARCHAR(64) NOT NULL, 'TAC_Val' TEXT NULL ) GO CREATE UNIQUE INDEX IX_TAC_ID ON TAC_USR(TAC_ID, TAC_Attr) MySQL will not take this command GO Thanks in advance. --- [This E-mail scanned for viruses by Friend.ly.net.] - 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: SQL Syntax Help
On Fri, Jan 31, 2003 at 02:07:11PM -, Kevin Smith wrote: Hi All, Can anyone help me get this query working in MySQL, this was created using Access, but it doesn't port well for MySQL syntax: SELECT b.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, b.price*b.quantity AS total FROM (products AS p LEFT JOIN product_options AS po ON p.prod_id = po.prod_id) RIGHT JOIN (product_options AS po1 RIGHT JOIN (basket_header AS bh INNER JOIN basket AS b ON bh.basket_id = b.basket_id) ON po1.po_id = b.op_id) ON p.prod_id = b.prod_id GROUP BY b.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, b.price*b.quantity, bh.basket_id, p.options HAVING (((bh.basket_id)=4)); Try FROM (((basket_header AS bh INNER JOIN basket AS b ON bh.basket_id = b.basket_id) LEFT JOIN product_options AS po1 ON po1.po_id = b.op_id) LEFT JOIN products AS p ON p.prod_id = b.prod_id) LEFT JOIN product_options AS po ON p.prod_id = po.prod_id MySQL tends to be more finicky than Jet about how you group things. I haven't tried this, but I think it will avoid confusing the MySQL optimizer. Bob Hall - 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: opposite of this join
On Tue, Jan 21, 2003 at 08:43:29AM -0800, Josh L Bernardini wrote: I have three tables, people, events and epeople. epeople includes a person id and an event id and works as a list of people attending events in the database. The following query returns a list of all the people participating in a particular event given an event id. select people.id as pid, concat(lastname, , , firstname) as name from events left join epeople on events.id=epeople.eid left join people on epeople.pid=people.id where events.id=2; How could I get a list of all the people not participating? thought I would add to the where clause with: and people.id is null; but that returns an empty set. Right. Presumably, there's no entry in your join table to link a person to an event that they're not attending. I've got a entry in the MySQL SQL section of my website called Whether a row on one side of a many-to-many join is linked to a given row on the other side that has a solution. http://users.starpower.net/rjhalljr/Serve Bob Hall - 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: opposite of this join
On Tue, Jan 21, 2003 at 01:38:42PM -0800, Josh L Bernardini wrote: Bob, First of all, thank you. I never would have gotten here on my own. Only I am not quite there. Using your example, I have managed to list all the events a person is attending and not attending. Wondering if you might provide some further clues as to how to restrict the query to those events a person is not attending, or in you example, those items a person doesn't own. This step is towards the goal of returning all the people not attending an event - or not owning a desk. Also what is the significance of the value returned in the attends column? Here's what I've got: mysql SELECT events.event, - people.lastname, - Sum(epeople.eid = people.id) AS attends - FROM (events, people) LEFT JOIN epeople - ON events.id = epeople.eid - WHERE people.id=1 - Group by events.id; It's been a while since I worked with this query, so I'm not sure that this answer is correct. However, after quickly going over the article again, I believe you can treat the attends column as a boolean type. Add AND attends = FALSE to the WHERE clause to get the events they will not attend. If that works, it answers both of your questions. (You can also use attends = 0, but attends = FALSE is more self-documenting.) HTH Bob Hall - 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: opposite of this join
In Sum(), join people IDs to people IDs, not to event IDs. In the article, the SQL statement is mostly ok, but the explanation section proves, once again, that I shouldn't be proofreading my own stuff. I've cleaned it up, so there won't be any more errors until I look at it the next time. On Tue, Jan 21, 2003 at 07:34:47PM -0800, Josh L Bernardini wrote: Thats what was strange with the results. One of the meetings the user was attending had a value of 9, the other 0. Might just give up doing this and use 2 queries to accomplish the same so don't spend anymore time on it unless your curious yourself. thanks just the same, jb |-+ | | Bob Hall | | | rjhalljr@starpow| | | er.net | | || | | 01/21/2003 06:57 | | | PM | | || |-+ --| | | | To: [EMAIL PROTECTED] | | cc: | | Subject: Re: opposite of this join | --| On Tue, Jan 21, 2003 at 01:38:42PM -0800, Josh L Bernardini wrote: Bob, First of all, thank you. I never would have gotten here on my own. Only I am not quite there. Using your example, I have managed to list all the events a person is attending and not attending. Wondering if you might provide some further clues as to how to restrict the query to those events a person is not attending, or in you example, those items a person doesn't own. This step is towards the goal of returning all the people not attending an event - or not owning a desk. Also what is the significance of the value returned in the attends column? Here's what I've got: mysql SELECT events.event, - people.lastname, - Sum(epeople.eid = people.id) AS attends - FROM (events, people) LEFT JOIN epeople - ON events.id = epeople.eid - WHERE people.id=1 - Group by events.id; It's been a while since I worked with this query, so I'm not sure that this answer is correct. However, after quickly going over the article again, I believe you can treat the attends column as a boolean type. Add AND attends = FALSE to the WHERE clause to get the events they will not attend. If that works, it answers both of your questions. (You can also use attends = 0, but attends = FALSE is more self-documenting.) - 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: Connection ms access to mysql
On Fri, Jan 17, 2003 at 03:15:05AM -0500, mc 02 wrote: I've downloaded the necessary application MySQL 3.51 and myODBC for freeBSD. On the XP side i only downloaded myODBC. I played around the configuration but i still cant seem to export the files to my FreeBSD box. What am i doing wrong? Any help will be appreciated. I think the current port for FreeBSD is 3.23.54. If you've installed version 3.51.x, then you're using technology that's way too advanced for me. If you've installed 3.32.51, then upgrade to 3.23.54. If your port is an earlier version, cvsup the port and then upgrade. You say that you've downloaded it, but you don't mention installation and testing, so I'll ask a dumb question: Does the MySQL server run on your FreeBSD system? If the MySQL server runs on your FreeBSD system, the next question is: How are you trying to export your Jet tables? I assume that's what you mean when you refer to files. If you actually want to put your mdb files on FreeBSD, then you should be asking the freebsd-questions list about Samba. Bob Hall - 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: outer joins
On Fri, Mar 22, 2002 at 04:37:07PM +, ds wrote: Hi, did you get any answer ? I'm having the same problem. If i wanted all results from table1, even if they don't appear in table2 i would do like: SELECT . FROM table1 LEFT JOIN table2 ON (table1.id=table2.id) ... But how to join SELECT . FROM table1 LEFT JOIN table2 ON (table1.id=table2.id) ... and SELECT . FROM table2 LEFT JOIN table2 ON (table1.id=table2.id) ... table1. Idem for table2. Outer join: SELECT ... FROM ... LEFT JOIN ON ... UNION SELECT ... FROM... RIGHT JOIN ON ...; Bob Hall -- sql, query - 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: SubSelect Problem
On Tue, Mar 19, 2002 at 12:45:57AM -, Alex Speed wrote: Ok, no support for it (d'oh), so, how do I go about breaking it down into a join or something like that? - Original Message - From: Alex Speed [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 19, 2002 12:13 AM Subject: SubSelect Problem SELECT textid, title, description from structure where textid in (select subtopicid from subtopic where topicid='Top'); Off the top of my head: SELECT DISTINCT st.textid, st.title, st.description FROM structure st INNER JOIN subtopic su ON st.textid = su.textid WHERE su.topicid = 'Top'; I haven't run this, so I can't guarantee that it will work. Bob Hall -- Database, table, query. - 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 tables named 'column' cause problems.
On Sun, Mar 17, 2002 at 05:31:52AM +0100, Tozz wrote: Hey Column is a mysql reserved word. Just like desc or asc or tons of others. See here: http://www.mysql.com/doc/R/e/Reserved_words.html Dan Then, imho I think its stupid that MySQL lets you create tables with reserved words, but it stops you from making dumps. Still seems like a bug to me. Sir, naming a column column is generally considered a bad idea, regardless of the DBMS being used. It's like naming a variable variable. Bob Hall -- All my databases are named database, datafile, datafil, etc. The tables are all named table, tabell, datasett, etc. Every one who has to do maintenance on the databases hates me. It's MySQL's fault for letting me set them up that way. - 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 MySQL help with tables and records
On Sun, Mar 17, 2002 at 12:28:35AM -0500, Lionlike wrote: Hi, I need a little help as I am a beginner with db things. I'm building a database for technicians and help-desk problems where technical problem tickets are stored in one table, and technicians in another. Really I have two main questions. First, each problem may have more than one technician assigned. In the problems table were I have a technician column, should I build an array that holds all the primary keys for each technician assigned or should I build a third table that links multiple technicians to a single problem? And my second question is this, I select the technicians based on alphabetical order out of a list. Each time there is an occurrance of a problem,I go to the next tech in alpha order. How can I do this with MySQL? Or would I have to organize in alpha order using PHP and select my next record? I'd like to go with whatever doesn't absorb system resources too badly and it just seems like having 3 to 5 users accessing this system and having PHP alpha order the database over and over would do that for each occurrance. And how could I flag a record so that I would know where to get the next record once I have it in alpha order? Thanks, David Second question first: Create a little table that stores the ID for the last tech assigned a ticket. The next time a ticket is assigned, have your code retrieve the ID and search the tech table for the next ID. Your SELECT query can do any alpha ordering more efficiently than PHP. First question: Your tables are in a many-to-many relationship. Any tech is assigned one or more tickets, and any ticket is assigned to one or more techs. Use a join table to create the many-to-many relationship. The join table has only two columns: a tech ID column and a ticket ID column. Each time a new ticket is assigned, add one row to the join table for each tech assigned the ticket. Each row contains the tech ID and the ticket ID. This makes your SELECT queries a little more complicated, but in the long run it creates fewer problems than any other solution. Bob Hall - 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 MySQL relational?
On Fri, Mar 08, 2002 at 04:21:09AM +0200, savaidis wrote: Is MySQL a relational database? If yes, how is implemented a one to one and one to many relation? Sir, there is no such thing as a one-to-one or one-to-many relation. Those are joins, and they are implemented in queries, as is the case with all relational database management systems; e.g. Oracle, Sybase, Informix. Bob Hall -- And Pharaoh spake unto Moses 'Database, table, and query.' - 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 MySQL relational?
On Fri, Mar 08, 2002 at 02:35:59PM -0500, Bob Hall wrote: On Fri, Mar 08, 2002 at 04:21:09AM +0200, savaidis wrote: Is MySQL a relational database? If yes, how is implemented a one to one and one to many relation? Sir, there is no such thing as a one-to-one or one-to-many relation. Those are joins, and they are implemented in queries, as is the case with all relational database management systems; e.g. Oracle, Sybase, Informix. Yikes! I messed up that one. There are, of course, one-to-one and one-to-many relationships, but not relations. A relation is something different. Relationships are typically implemented as joins in queries. Most RDBMSs allow you to define primary-key/foreign-key relationships, and enforce them with varying levels of integrity constraints. They often use the PK/FK definitions to automate joins. MySQL follows the standard syntax for MAKE TABLE statements, but doesn't do anything with the FK clause. That means that there's no integrity constraint to enforce relational integrity on the PK/FK relationship. I think I did a little better the second time around. Bob Hall -- And Moses spake unto Pharaoh Database, table, and query? Codd of our fathers, what are you talking about!' - 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: Comments in Documentation
On Sat, Feb 23, 2002 at 07:28:04PM +0200, savaidis wrote: Instead of binary it sould be better to use a case conversion flag with reverse function - NOT set by default. Binary doesn't make sence to me. As for LIKE I think most WHERE commands use it so it is obvious it should be there. Sir, I'm a database developer. I write a lot of SQL statements, 99% of which have WHERE clauses. I doubt that I've used LIKE as much as three times in the last year. I also like the comparison settings the way they are. The manual is not intended to teach you to use MySQL. It's intended as a comprehensive reference document. If you want a primer, buy Paul's book. If you want something free and online for beginners, cruise to the MySQL FAQ. I learned my first programming language from the vendor's reference manual. I don't recommend it. Bob Hall -- Database, table, query, eggs, milk, bagels, cheese, cabbage, salt. Now what did I do with my shopping list? - 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: I must ask.
On Fri, Feb 08, 2002 at 06:10:34PM -, DL Neil wrote: There are some RDBMS-es that implement SQL to the point of apparently setting up a construct that links tables - foreign key based data integrity being the first example that springs to (my) mind. However relational logic suggests that the way to set up a linkage between tables is to show this in dynamic logic - your SQL statement, eg SELECT * FROM tbl1, tbl2 WHERE tbl1.PrimaryKey = tbl2.ForeignKey; will produce a result-row for every intersection 'match' between the two tables. One of the MAJOR advances of relational databases over their predecessors was this move 'away' from structural 'connections'. However these have been maintained as 'features' by some, usually to promote speed/efficiency. Speed/efficiency is a minor issue. Integrity constraints are used to prevent people from writing queries that violate relational integrity rules; e.g. create orphan records. And integrity constraints are unrelated to joins, except in the most incidental way. It is possible to use them to create joins, but that's not why they're built into databases. Bob Hall -- Database - 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: calculated fields
On Fri, Jan 25, 2002 at 01:06:12PM +1030, Graham wrote: I have looked at the documentation and have been unable to locate any information in respect of calculated fields. Can anyone verify the lack of this feature in mysql or point me to the area of the documentation which discusses them if the are indeed included. If they are not available has anyone suggestions for how I may work around this lack of functionality. Sir, calculated columns violate third normal form. The standard way of creating calculated columns is to use a query. Bob Hall -- database, table, roofing cement - 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: Porting from MS SQL to MySQL
We've requested a database from different companies, and specifically said we wanted MySQL or PostgreSQL because of the open source angle and we're a library. One company offered MS SQL as the platform and said that they can later on port it to MySQL. For this they wanted 18 000 euro. Now, what I want to know is, how easy is it to port a (fairly complicated) database from MS SQL to MySQL? It can't be work worth 18 000 euro, now can it? Looking at it strictly from your point of view, I can think of only two reasons to accept the bid. 1) The price is so much lower than any other bid that it is worth the aggravation of installing the system and going through the shake-down period, and then re-installing a different version of the system and going through another shake-down period. 2) You need the system NOW, and the bidder has an SQL Server system already developed that will fit your needs and can be installed immediately. It doesn't sound like either of these two conditions describe your situation. Bob Hall - 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: A difficutl query I cannot do.
On Thu, Jan 10, 2002 at 10:24:37AM +1100, Sam Russo wrote: I have a delimited file sent to me with students ID subjects and when they do these subjects. This file (table) looks like: ID TimeSlotSubject 215 3 Eng1 648 2 Maths2 901 4 French 215 2 Maths1 901 1 Science2 648 4 Art 215 1 Science1 901 3 Eng1 I need to produce the following output (on a web page using php and mysql) with a mysql query. ID1 2 3 4 215 Science1Maths1 Eng1 648 Maths2 Art 901 Science2Eng1 French You need to cross tabulate the data. http://www.mysql.com/articles/wizard/index.html Bob Hall - 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: Left join?
On Thu, Dec 27, 2001 at 01:21:08PM -0700, Ken Kinder wrote: I guess I'm just old fashion. I learned with the terms inner and outer joins, and I conceptually in struggling with this left/right join stuff -- I thought the order it joins the tables was simply a matter of optimizer logic. That's true for inner joins. The tables in an outer join are treated differently, so order matters. One and only one table in an outer join is preserved, and you have to specify which table is preserved. When a table is preserved, all rows are returned, even the rows that don't have a match in the other table. (The WHERE clause may prevent some rows in the preserved table from being returned.) Can someone give me a rule of thumb in understanding what this left/right join business is vs inner/outer joins? In a left outer join, the table to the left of the LEFT JOIN keywords is preserved. In a right outer join, the table to the right of the RIGHT JOIN keywords is preserved. So a LEFT JOIN b is the same as b RIGHT JOIN a Table a is preserved in both cases. Bob Hall - 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: To Mr Bob Hall (and everybody else who wants to read it)
On Thu, Dec 27, 2001 at 08:43:30PM +0100, Carsten H. Pedersen wrote: cut a'lot In the end: 1) Pass-through Queries don't give me any problem with stupid PopUp forms asking for confirm, but can not be used if I must refer to a MS Access Object in the sintax of the query. Sir, I missed the post that this was quoted from. Why can't you grab the value contained in the object in your VBA code and add the value to the pass-through query on the fly? Strictly speaking, we're talking about semantics here, not syntax. The object reference is in the right place, but MySQL doesn't understand it. 2) the Action Queries can read MS Access objects but need to be confirned each time I execute them. If you consider that in some macroes I have 4 or5 Action Queries one after the other, you will understand that it is very unconfortable. So, as there's no way of making MySQL read data in MS Access Object, is there a way to avoid stupid PopUp forms asking for confirm to an Action Query? Can't you simply set your localized equvalent of SetWarnings to off? (in access-vb that'd be DoCmd.SetWarnings Off. It can be set in DoCmd.SetWarnings False a macro as well, though I wouldn't hazard to guess the Italian translation). You can also turn off the warnings globally, on the Edit tab in Tools/Options. Uncheck the appropriate boxes under 'Confirm'. Carsten's solution is probably better in most situations. BTW, this thread is no longer MySQL related. This and most other Access development questions are answered in the Access help files. Greetings from Italy And some from Denmark, as well... I lige maade. Bob Hall -- Database, table, query - 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: Normalization advice needed
On Thu, Dec 27, 2001 at 04:01:57PM -0600, Gawain wrote: I've got a 3500 record database cataloging an art collection. The data was originally assembled (by others) in Access. I've imported it into MySQL to make it available on the artist's web site: http://maryloureifsnyder.com. The data was formatted in Access as one big table. About 800 of the pieces in the collection are works in a series. In the artist's catalog there's about 20 books, each containing 40 or so pages. The original creators of the database developed a decimal numbering scheme and simply duplicated most of the data in the records describing these works. For instance, records 129.00 to 129.43 contain mostly the same data about the title, condition, storage, etc. with only minimal (if any) changes. My question is, is it worth the effort to normalize this data? If so, what would the best method be? Whether it's worth the effort depends on how much effort is involved and whether you can afford the time. (I'm assuming that you are volunteering your time. If not, then it depends on whether your client can afford your time.) Normalized tables are the best solution, but not necessarily the optimal solution. How much time is involved depends on the condition of the data now and how good you are at writing the SQL statements and procedural routines necessary to clean up the data and normalize it. Almost all clean up can be done in SQL, if you have the skill. If you don't, creating procedural routines takes more time. I can't tell from your description how normalized (or denormalized) your data is. Without looking at the data, I can't tell you how clean it is. And I don't know how much time can be devoted to it. Since I don't know any of the values for the optimization problem parameters, I can't tell you whether it is worth it, or how far to carry it. I can tell you that if the data was entered without any data validation, and if the column data types were not carefully chosen, then the data will need a lot of cleaning before you start normalizing. The best method for normalization is the only method. Apply the normal forms in order. First normal form is better than no normalization. Second normal form is better than first normal form. And so on. I've never seen anyone take it past Boyce-Codd normal form. Take it that far if you can. Bob Hall - 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: Question of a Beginner
On Wed, Dec 26, 2001 at 05:34:34PM -0500, Jose Villanueva wrote: I'm a beginner in MYSQL, please can anyone tell me the right command in ORACLE equivalent to the EXEC SP of SQL Server, i'm working with ORACLE SP, i will apreciate any hepl, thanks. Sir, I'm confused. If you want the ORACLE equivalen of EXEC SP, you need to post to an ORACLE forum. If you want the MySQL equivalent, please explain what EXEC SP is. Bob Hall -- In the room women come and go, Speaking of database, query, and table.T-Sql Elliot - 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: SQL sintax problem of a biginner
On Thu, Dec 27, 2001 at 12:11:17AM +0100, [EMAIL PROTECTED] wrote: Hi everybody, I really hope that there will be somebody who will waste a bit of his time helping a very, very beginner. I have a front-end database in MS Access that gets data from a MySQL back-end. When I execute a Pass-through Query it works fine if I write: SELECT Orders.Id, Orders.Number, Orders.Clients_Id FROM Orders WHERE Orders.Clients_Id=234 (or whatever number I put in) Instead if I try to get the WHERE Clause from a form which is obviously alredy open in my MS Access front-end and I write: SELECT Orders.Id, Orders.Number, Orders.Clients_Id FROM Orders WHERE Orders.Clients_Id=[Forms]![Clients]![Id] I get an ODBC error: can't connect I tried then to declare [Forms]![Clients]![Id] as a PARAMETER (using SET @...) but, whatever I do and whatever I write, I get a SINTAX ERROR MESSAGE. First, let me get the inevitable joke out of the way. I didn't know they taxed that in Italy. Sir, when you write a pass-through query, everything in the SQL statement is a literal value, and the backend does all the processing. So your pass-through query is asking MySQL to find the Clients_Id in the Orders table that matches the value '[Forms]![Clients]![Id]'. MySQL can't make any sense of this, so it returns an error message. Grab the ID value before writing your query. Then insert the actual ID value into the pass-through query when you write it. So if the ID is 248, your WHERE clause would be WHERE Orders.Clients_Id = 248; Remember that MySQL can't read data in Access objects. It can only process whatever is passed to it via ODBC. Bob Hall -- In the room women come and go, Speaking of database, query, and table.T-Sql Elliot - 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: Nested Inner Joins
On Tue, Dec 11, 2001 at 07:34:11PM -0800, Kendra L Knudtzon wrote: I am having problems with this nested inner join statement: SELECT tblMethodType.MethodType, tblMethodParm.MethodName, tblParm.ParmName, tblParm.Label, tblParm.Value, tblParm.Unit, tblParm.Tip FROM tblMethodType INNER JOIN (tblMethod INNER JOIN (tblParm INNER JOIN tblMethodParm ON tblParm.idParm = tblMethodParm.idParm) ON tblMethod.MethodName = tblMethodParm.MethodName) ON tblMethodType.MethodType = tblMethod.MethodType; I have not had much experience with INNER JOINS, and I read one website that said the MySql can't support grouped inner joins... If this is true, how do I get this type of command to execute under MySql (I inherited this code but the statement apparently worked with Microsoft Access) MySQL supports grouped INNER JOINs just fine. But you can't put an inner INNER JOIN between the outer INNER JOIN and the ON keyword. Try FROM (inner INNER JOIN clause) INNER JOIN tblMethodType ON ... Do the same thing with the innermost INNER JOIN clause. What you typed above works fine in Access because Access doesn't care about the order. Bob Hall -- Kanskje, kanskje en gang - sier du til meg - database, table, query RolfJacobSQL - 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: Hopefully easy SQL question
On Wed, Nov 14, 2001 at 09:03:11PM -0800, Christopher Oson wrote: Good Day All, I have a question mysql select storyID, storyCategoryID, left(title, 25) title - from newsStories where storyCategoryID 0 - order by storyCategoryID; +-+-+---+ | storyID | storyCategoryID | title | +-+-+---+ |4327 | 1 | New Artery Device OK'd| |4328 | 1 | Pushing 'Low Carcinogen' | |4383 | 1 | Lasting Relationships | |4384 | 1 | Anthrax Briefing | |4385 | 1 | Varicose Veins| |4513 | 2 | Letters To God| |4380 | 3 | United We Stand | |4381 | 3 | Tea Station | |4382 | 3 | Christmas In July | |4533 | 7 | test | +-+-+---+ What I want is a query that will retrieve the MAX(storyID) from *each* storyCategoryID. I know this is possible with a sub-select and/or a union. But is this possible without a sub-query or having to run multiple queries? The storyID's that should be returned should be: 4385 - 1 4513 - 2 4382 - 3 4533 - 7 Sir, try a self join. Do a standard aggregate query to get the max storyIDs, GROUP BY the category ID, and join the max storyIDs to the storyIDs of the second instance of the table. Bob Hall -- Kanskje, kanskje en gang - sier du til meg - database, table, query RolfJacobSQL - 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: My Book (WAS Re: MySQL Developer's Handbook)
On Mon, Nov 12, 2001 at 01:36:45PM -0800, Jeremy Zawodny wrote: Just *today* I signed a contract with O'Reilly Associates to write Advanced MySQL which I've begun working on. The goal is to have it out in the 2nd half of next year. We've been discussing it for a few months now, and we're all convinced that it's a necessary book and that now is the time to do it. It will cover MySQL 4.x (probably 4.1 based on the schedules I've heard). It will cover Advanced topics meaning that it will hit some that the other books have not and it will go into greater depth on some of the topics that do appear in the more beginner-oriented books. Can I request that you include a chapter on using MySQL with FreeBSD? If I recall correctly, that's something you should be able to write about. I'd be glad to send you the working TOC outline for feedback. If you're looking for kibbitzers, why not just post the TOC to the mailing list? Bob Hall -- In the room women come and go, Speaking of database, query, and table.T-Sql Elliot - 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 a query ...
On Fri, Nov 02, 2001 at 06:15:08PM -0800, John Kelly wrote: Hi, I have a MySQL table with a column that contains some of a web site directory's category names in the format: Sports:Football:Players I am trying to build a query that that locates all records that match the above category name OR if none exist its parent Sports:Football OR if none exist its parent Sports. The top level category, in this case Sports, will always have at least one matching record. I know I can do this with multiple queries by checking the previous query's result, but I am trying to build a query that does it all in one lookup to avoid lots of lookups in deep categories. Something along the logical lines of ... SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF NONE category = 'Sports:Football' OR IF NONE category = 'Sports' ... of course the above query does not work but if anyone knows of how to accomplish something similar in one query I would much appreciate it. Sir, the problem is that relational databases use set logic, and you are trying to find a solution in sequential logic. You need three tables. I'll call them Cat1, Cat2, and Cat3, but you should rename them to whatever makes sense to you. They will hold, respectively, 'Sports', 'Football', and 'Players'. (Or 'Business', 'Industry', and 'Firms'. Or whatever.) The table structure would be something like: Cat1(Cat1ID, Category) Cat2(Cat1ID, Cat2ID, Category) Cat3(Cat2ID, Cat3ID, Category) A sample row in Cat1 would be (1, 'Sports') A sample row in Cat2 would be (1, 1, 'Football') A sample row in Cat3 would be (1, 1, 'Players') (If you're not concerned about economizing on storage, you could eliminate the IDs and have each child record refer to the parent category instead of the parent ID. In that case, you don't need the Cat1 table, which serves only to match a category with its ID.) The query would be SELECT Cat1.Category, Cat2.Category, Cat3.Category FROM (Cat1 LEFT JOIN Cat2 ON Cat1.Cat1ID = Cat2.Cat1ID) LEFT JOIN Cat3 ON Cat2.Cat2ID = Cat3.Cat2ID WHERE Cat1.Category = 'Sports' AND (Cat2.Category = 'Football' OR Cat2.Category IS NULL) AND (Cat3.Category = 'Players' OR Cat3.Category IS NULL); If you want the output in the 'Sports:Football:Players' form, you can rewrite the SELECT clause as SELECT Concat(Cat1.Category, If(IsNull(Cat2.Category), '', ':'), Cat2.Category, If(IsNull(Cat3.Category), '', ':'), Cat3.Category) Disclaimer: I haven't run this, so I might have gotten some of the details wrong. Bob Hall - 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: ADO/ODBC question
On Mon, Jul 30, 2001 at 12:18:02PM +0100, Christopher Thorpe wrote: Hi all quick question if I use a query such as the one below INSERT INTO usq (usq_id,usq_querystring) VALUES(NULL,'test'); SELECT LAST_INSERT_ID(); how do I push this two part query through an ADO command object and how do I get the variable back from the select part of the query... alternatively should I do it as a recordset update? Sir, the answer is yes and no. An ADO command object doesn't return a value from an SQL statement, so you have to use a recordset object to get the return value from the Last_insert_id function. I would do the first through a command object, and the second through a recordset. It would probably work just as well to use them sequentially as the CommandText property of of a command object and execute them by opening a recordset. As you are probably aware, there's rarely only two ways of doing something with ADO. I don't think you can use them simultaneously in a single command object. But there's no need to; Last_insert-id() returns the last insert id for the current connection, regardless of what else has been inserted in the interim. As long as your connection object stays open, you'll get the correct return value. I would never use rs.Update to do something that can be done with an SQL statement. My experience is that recordsets take about three to four times as long as SQL when doing single row inserts over native connections. For code examples, see your friendly neighborhood MS help files. I've devoted more than enough space on a MySQL list to ADO. -- Bob HallHow to invoke the MySQL list daemon: Sql, table, query and database, Distribute this mail and be quick, potato face! - 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.org
On Fri, Jul 20, 2001 at 12:29:52PM -0400, Britt Johnston wrote: MySQL AB and NuSphere had a meeting over the phone, we exchanged information and opinions and NuSphere will propose times for the next meeting. Britt... Britt, thanks for the update. The neutral tone and lack of result make it sound like you had a frank exchange of views. Since MySQL AB initiated posting events here as they occur, and few people complained about it, I see no reason why either side shouldn't continue. -- Bob Hall mysql list incantation: sql table database query - 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.org
On Fri, Jul 20, 2001 at 12:29:52PM -0400, Britt Johnston wrote: MySQL AB and NuSphere had a meeting over the phone, we exchanged information and opinions and NuSphere will propose times for the next meeting. Britt... Britt, thanks for the update. The neutral tone and lack of substantial result make it sound like you had a frank exchange of views. Since MySQL AB initiated posting events here as they occur, and few people complained about it, I see no reason why either side shouldn't continue, unless we plan to ban all discussion of the matter. -- Bob Hall mysql list incantation: sql table database query - 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.org
On Thu, Jul 19, 2001 at 09:17:31AM -0400, Bob Hall wrote: I think I've made it clear that I'm not enamored of NuSphere's recent actions, which I think are remarkably bone-headed. At I'm afraid the emotions aroused by recent events have stripped away my thin veneer of reasonable civility and exposed the harsh, arrogant, stunted human being underneath. :) But seriously, I'm taking advantage of the occasion of a mild rebuke delivered privately to renew my commitment to treating everyone with respect. At least until the next bone-headedd7B Bob Hall How to invoke the MySQL list sendmail daemon: Database, dataspace, Sql query. Destribute my e-mail, Be quick and don't tarry! - 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.org
On Wed, Jul 18, 2001 at 11:10:20AM -0700, Michael Collins wrote: It seems to me that the bottom line is that any outcome that results in there being a fork in the development of MySQL (with the result being two completely different types of MySQL), or in NuSphere retaining control of the mysql.org domain should be vehemently rejected by the MySQL community. No matter what agreement may or may not have been created, since MySQL AB has made it clear that this is not what they want or intended. So to me it is obvious that any agreement that may have been made is either being misinterpreted by NuSphere or was not entered in good faith. NuSphere, if what you want is an improved MySQL and if you have anything to contribute to the betterment of MySQL, hand over the code. If you want a non-profit Web site first turn over MySQL.org to MySQL AB and either register NuSphere.org for your non-profit site or help MySQL AB to get it started. If you want to make money on MySQL, follow the guidelines of GPL to the letter and to the approval of MySQL or pay the per-copy license. I guess the solution seems so simple for an outsider. -- Michael I think I've made it clear that I'm not enamored of NuSphere's recent actions, which I think are remarkably bone-headed. At the same time, a NuSphere-controlled mysql.org doesn't strike me as a disaster, provided they can do it with out shooting themselves in the foot, as they are doing now. I have two concerns about the present conflict. First is the obvious problem that MySQL AB lacks the resources to develop table types with extended functionality, as NuSphere, SleepyCat, and InnoDB are doing. MySQL AB has a comparative advantage in SQL interfaces, core DBMS functionality, and porting MySQL to as many OSs as possible. If they try to do more than that, they'll probably be too overextended to do any single thing well. NuSphere has a comparative advantage in providing extended functionality. Because they don't have to deal with the problems that MySQL AB is solving, and because they have an existing code base that they can modify, they can bring a relatively mature product to market that makes the overall MySQL package more attractive. Even if you discount the marketing hype, the word from the beta testers is good. My second concern is the fact that NuSphere has made a large investment in converting their table to use with MySQL, and that investment was made after undergoing a formal agreement. If the investment fails and the agreement ends in lawsuits, it will discourage further investment and more agreements involving other companies. On the other hand, if the present disagreement can be resolved without recourse to legal remedy and NuSphere makes a nice profit on its investment, it will encourage other companies to make agreements with MySQL AB and make investments that further extend MySQL's capabilities. It's in everone's interests for NuSphere to make a profit, and it's in everone's interests for MySQL AB to have a reputation as a reasonable business partner. The developer community needs both the complementary capabilities of NuSphere and MySQL AB, and useful tools from other companies. The tools won't come unless other companies invest, and they won't invest unless they think they can make a profit. Disagreements between partners raise costs and reduce profit. I don't want to discourage either side from defending their interests or what they see as their rights. An agreement that leaves one side or the other feeling ripped off is not going to encourage more agreements or more investment. On the other hand, failure to come to agreement will poison the communal well. In the end, the details of an agreement will be less important than the fact of an agreement, provided the agreement promotes both parties' interests. NuSphere doesn't need mysql.org in order to make a profit, and MySQL AB can allow some use, perhaps temporarily, of the MySQL trademark without surrendering the crown jewels. MySQL AB's primary assets are its leadership in the MySQL community and the company's comparative advantage in the kinds of development mentioned above. The real issues are: How is NuSphere going to market their product, what is MySLQ AB going to contribute that both helps NuSphere and helps create the perception of an atmosphere that welcomes outsiders, and what is the value of MySQL AB's contribution (i.e. how much is NuSphere going to pay for an agreement, either in cash or otherwise). -- Bob Hall mysql list incantation: sql table databse query - 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
Re: group by concat
Hi! My problem is when I use group by concat(col1,col2) when col1 and col2 is primary keys and col1 is varchar(255): SELECT col1,col2 FROM Table1 GROUP BY concat(col1,col2); - return only one row - it is bad SELECT col1,col2 FROM Table1 GROUP BY concat(col1,col1,col2); - return 48 rows - it is ok, but concat(col1,col1,col2) is stupid I change col1 to varchar(155): SELECT col1,col2 FROM Table1 GROUP BY concat(col1,col2); - return 48 rows - it is ok My platform: Linux x86, MySQL 3.23.39, table is myisam format. Is it bug or feature? Thanks Marek Sir, what does GROUP BY Concat(col1, col2) do that GROUP BY col1, col2 does not? In other words, I don't see any point in using Concat(). Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: linking columns
Hi everyone! I'm starting with mysql! I wish to know if it is possible to make a table that has to columns linked by a mathematic expression. For example: My table is my_tbl; And it has three colums: column_1, column_2 and column_3; Every column have int or float values; And for example, I want that the third column always be equal to column_1 * column_2; So when I change a value in column_1 or column_2, then column_3 will be change imediatly by mysql! Is that possible? how can I do it? Thanks... Daniel Sir, use a query to return calculated columns. Don't put them in tables unless you're going to delete or replace the values the calculation was based on and you need to store the results of the calculation. Bob Hall [EMAIL PROTECTED] Know thyself? Absurd direction! Command 'know' not recognized. Drat. I wanted a list server and got an epistemological skeptic instead. MySQL secret passwords: sql query database - 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: Strange pseudo-coded query
Rolf Hopkins wrote: This is way off topic and you should seek an MS Access help list. Excuse me. I am working in MySQL. I see no reference whatever to MS Access in my message. How do you figure this is off topic? Sir, Rolf had no way of knowing that you didn't know that you were dealing with SQL statements generated by MS Access wizards. Parts of the statements have been altered by hand, but the over all style is unmistakable. For those of us familiar with MS Access, it is obvious that the SQL is not psuedo-SQL, but actual SQL created by MS Access and referring to objects in an Access application. [Zip Code Distance Extractor] could refer to any number of diff things such as a column in a table, a table name, a field on a form. I suppose it could. There is no such table or column listed in the documentation that comes with this product. It is a form in an Access application. From the SQL, you can determine some of the names of the fields on the form. But that's all I can tell you about it. The ! also has several different meanings and you should be able to find it in help files. Help files for what? According to the books I have, the ! means NOT in the context of a SELECT clause. In the context, it means 'menber of'. [Zip Code Distance Extractor] is a form which is a member of the Forms collection (an object in an Access database). [Zip Code] is a field which is a member of the [Zip Code Distance Extractor] form. Rolf quite naturally assumed that you were working with an Access database application (and so did I), and was trying to direct you to the Access help files. Bob Hall [EMAIL PROTECTED] Know thyself? Absurd direction! Command 'know' not recognized. Drat. I wanted a list server and got an epistemological skeptic instead. MySQL secret passwords: sql query database - 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: Very difficult Query! Help plz!!
hi all together! i'm turning to your mailinglist as last chance to find s.o. helping me with my problem. the situation is as follows: i got a table on a mysql-database, queriing it by php4. the table consists of all planets of the planetarion.com-system. these information consists of each 3 coordinates of a planet (x, y and z), identifiing a planet clearly. theese 3 coords are written in a int-field, each. other information are the name of the planet, the rulers name, the score and the size of the planet. when the first two coords (x and y) of 2 planet are equal, the planets belong to the same galaxie. and now the point: Sir, that means that all the planets of a given galaxy are located on a single line (vector). I've heard of disk galaxies, where all the objects have the same value (give or take a few million kilometers) on one coordinate. Having as few as three planets in just one solar system temporarily sharing values on two coordinates has unusual results, e.g. three wise men wandering the Middle East on Christmas Eve looking for hummus to dip their falafel in. i try queriing the database as follows: the user inserts some wished dates (the lowest score, the searched planet may have, the highest, the highest number of roids and the lowest..) and the number-interval in wich the _galaxy-score_ may be included. (means x galaxy-sore y). the problem is, to query that! the first query is very simple: SELECT * FROM Planetarion WHERE Score $x AND Score $y AND Size $a AND Size $B. how how you get the galaxie-score for that?? the second query alone is very simple, too: SELECT SUM(Score) as Summe FROM $table WHERE X = $x AND Y = $y GROUP BY X, Y. _But how to combine both queries???_ i tried getting the first one and process for each elemnt of it the second one... but then the resources of my server will get low, when the first query results eg. 300 elements or more i tried asking the german newsgroup and other people i know, using mysql. and you're my very last hope to solve this problem thank you very much for each help!! regards, dotcom What you are trying to do isn't clear. As written, your queries can't be combined. An SQL statement that managed to combine those two statements would be comparable to a galaxy in which all the planets had the same values on two coordinates; interesting, but too weird to avoid falling apart. However, if you are trying to group the results of the first query and sum the scores in each group, the following should work: SELECT Sum(Score) FROM Planetarion WHERE Score $x AND Score $y AND Size $a AND Size $B GROUP BY X, Y; Bob Hall [EMAIL PROTECTED] Know thyself? Absurd direction! Command 'know' not recognized. Æsj. Jeg søkte etter en list server og fant en epistemologisk skeptiker. MySQL secret passwords: sql query database - 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 query for duplicate records
Hi, How would a word a select statment, to search a database for duplicate entries in one field. For example, the fields: device, hostname, IP, comments I want to find all instances where there my be two devices with the same hostname. Thanks! Pete Sir, try the following. SELECT DISTINCT device, hostname FROM table_name GROUP BY hostname HAVING Count(*) 1; Only one of the two or more devices will be listed. If you want to include cases where the same device has been listed twice with the same hostname, leave out the DISTINCT. WOMM (Works On My Machine) Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: Good book for MySQL and Perl DBI?
Does anyone have any recommendations for a good book on MySQL SQL? What about PerlDBI (I noticed that the O'Reilly one is over a year old)? Just realizing that there's so much out there that I haven't been told... -Chris Paul Dubois and New Riders are releasing 'MySQL and Pearl for the Web'. If that's what you're interested in, the book is due at the end of July. There's a little information about the book at www.newriders.com. I'm going through a review copy and it looks pretty good. The publisher said that I could say that. :) Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: VB, SQL: ...WHERE Mitglieder.geb='1978-08-26' doesn't work
I'm using Visual Basic with DAO 3.6 and I try to execute this query dim rsPersonen as dao.recordset Set rsPersonen = db.OpenRecordset( _ SELECT Mitglieder.* FROM Mitglieder + _ WHERE Mitglieder.geb='1978-08-26') Here I get an Error-Message: Runtime-Error 3464, ... With DAO, the query has to pass through the Jet Engine, so you need to adjust for Jet's idiosyncrasies. WHERE Mitglieder.geb = #1978-08-26# That's my best guess. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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 query urgent!!!
i, I have to write simple mysql script. I need folowing things: Number of people logged in previous 0 to 24 hours Number of people logged in previous 24.5 to 48 hours Number of people logged in previous 48.5 to 72 hours Number of people logged in previous 72.5 hours to 7 days Number of people logged in previous 7.5 to 14 days Number of people logged in previous 14.5 days onwards My tables is as follows: mysql select * from User limit 1; +--+---+-+--+ | partner_name | user_name | last_login | mailbox_size | +--+---+-+--+ | foo.net | edward| 2001-06-02 09:37:41 | 229099 | +--+---+-+--+ 1 row in set (0.62 sec) I also want to save the output to comma delimited file. Cheers kapil Sir, redefine the last_login column as a timestamp and try the following; CREATE TEMPORARY TABLE time_groups SELECT If( Unix_timestamp(CURRENT_TIMESTAMP) - Unix_timestamp(last_login) = 86400 , '01 day' , If( Unix_timestamp(CURRENT_TIMESTAMP) - Unix_timestamp(last_login) = 172800 , '02 days' , If( Unix_timestamp(CURRENT_TIMESTAMP) - Unix_timestamp(last_login) = 259200 , '03 days' , If( Unix_timestamp(CURRENT_TIMESTAMP) - Unix_timestamp(last_login) = 604800 , '07 days' , If( Unix_timestamp(CURRENT_TIMESTAMP) - Unix_timestamp(last_login) = 1209600 , '14 days' , '14 days' ) ) ) ) ) AS time_group FROM User; SELECT time_group, Count(*) FROM time_groups GROUP BY time_group; It works on my machine. The time_group values are chosen so that they will display in the proper order. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: SQL question
I'm having a lot of difficulty trying to figure this out. I have a table with a list of projects that I would like to arrange and view as a tree. This is my table: ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | name | varchar(64) | | MUL | || | handle | varchar(18) | | | || | parent | int(10) unsigned | | | 0 || | status | varchar(64) | | | || | data | text | YES | | NULL|| ++--+--+-+-++ This is what I want to get in the end: + root project | + project 1 | | | + sub project 1.1 | + sub project 1.2 || |+ sub project 1.2.1 | + project 2 | + sub project of 2.1 + sub project of 2.2 What SQL statement(s) would retrieve my records, ordered by project ID, but grouped recursively into a tree? Or, is this something not possible through SQL itself? Thanks, Davis Sir, SQL can handle trees. Regular readers of this list will be very surprised to see me recommend the book 'SQL For Smarties' by Joe Celko. There are two chapters on tree structures. You seem to be using the adjacency list model, so read that chapter first. Parts of his code assume a procedural language (e.g. PL/SQL or Transact-SQL), so you will need a client app to fully implement the solutions using MySQL. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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 to the same table multiple times
Ok, I've answered my own question but now have another.
How do I reference the sitename for the 3 sites?
sitename returns the last sitename for all 3 I tried S1.sitename etc. but it
doesn't work.
Sir, in what way does it fail to work?
$query = SELECT * FROM department LEFT JOIN sites S1 ON
deptsite1=S1.sitekey LEFT JOIN sites S2 ON deptsite2=S2.sitekey ORDER BY
$order $dir;
What's the $dir for? Does this statement fail in both PHP and the
MySQL interface, or only in PHP?
You can avoid dealing with aliases entirely if you normalize the
department table.
Bob Hall
$result = mysql_db_query($dbName,$query);
while ($r=mysql_fetch_array($result)) {
echo tr bgcolor=$colorvalues
tdfont size=\-1\$r[deptdesc]/td
/tr
tr bgcolor=$colorvalues
tdfont size=\-1\$r[sitename]/td
tdfont size=\-1\$r[deptphone1]/td
tdfont size=\-1\$r[deptfax1]/td
tdfont size=\-1\$r[deptemail1]/td
tdfont size=\-1\$r[deptmobile1]/td/tr
tdfont size=\-1\$r[sitename]/td
tdfont size=\-1\$r[deptphone2]/td
tdfont size=\-1\$r[deptfax2]/td
tdfont size=\-1\$r[deptemail2]/td
tdfont size=\-1\$r[deptmobile2]/td/tr
tdfont size=\-1\$r[sitename]/td
tdfont size=\-1\$r[deptphone3]/td
tdfont size=\-1\$r[deptfax3]/td
tdfont size=\-1\$r[deptemail3]/td
tdfont size=\-1\$r[deptmobile3]/td;
-Original Message-
From: Ross Goonan [mailto:[EMAIL PROTECTED]]
Sent: Friday, 8 June 2001 11:47
To: [EMAIL PROTECTED]
Subject: JOIN to the same table multiple times
###
Creating a Telephone / Information Directory with MySQL / PHP3
People belong to a department a site.
Need to be able to:
List all people
List all people within a Department
List all people within a site
List all people within a Department Site
Have set up Department table with site1, site2 site3 as the same
department
exists in multiple sites.
When listing a site, I need to JOIN the site table multiple times to get the
site name.
SELECT * FROM Department LEFT JOIN Site ON Site1=Sitekey
- Works no worries
SELECT * FROM Department LEFT JOIN Site ON Site1=Sitekey LEFT JOIN Site ON
Site2=Sitekey
- Error 1066: Not unique table/alias 'Site'
SELECT * FROM Department LEFT JOIN Site ON Site1=Sitekey LEFT JOIN Site AS
Sitetable ON Site2=Sitekey
- Error 1052: Column 'Sitekey' in on clause is ambiguos
###
rpm -qa | grep SQL
MySQL-3.23.33-1
MySQL-client-3.23.33-1
MySQL-devel-3.23.33-1
rpm -qa | grep sql
php-mysql-3.0.16-2bc
perl-Msql-Mysql-modules-1.2210-2
###
People Table
Surname
Firstname
Department
site
Department Table
Name
Site1
site2
site3
Site Table
Sitekey
Sitename
###
-
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 mysql-unsubscribe-##L=##[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 mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database
-
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 mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Update phone inner join tmpphone....
I tried this query: Update phone inner join tmpphone on phone.empno = tmpphone.empno set phone.lname = tmpphone.lname, phone.fname = tmpphone.fname and it didn't work. This works in MSAccess. Is this syntax (or something like it) supported by MySQL?? No sir. Updating a table based on data in another table won't be supported until version 4 comes out. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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-problems
Hi there, I have five tables in the database: customers, orders, ordercontents, products and payments. The problem is that I should get a list which shows some kind of a ledger list (shows who has paid and so on). My first try looks like this: SELECT orders.OrderID, customers.Name, SUM(ordercontents.Amount * products.Price) AS TotalSum, SUM(payments.PaidFIM) AS TotalPaid FROM orders LEFT JOIN customers ON (customers.CustID = orders.CustID) LEFT JOIN payments ON (payments.OrderID = orders.OrderID) LEFT JOIN ordercontents ON (ordercontents.OrderID = orders.OrderID) LEFT JOIN products ON (products.ProdID = ordercontents.ProdID) WHERE orders.Cancelled=0 AND ordercontents.Removed=0; That's it... I hope that you understood the structure. Also there should be noticed that the order can have many different products ordered (listed on ordercontents -table) and also that there can be many separated payments for same order. Now this query works, but it doesn't return correct amounts for TotalPaid and TotalSum -columns. Thanks for help, - Ville Mattila, Ikaalinen, Finland Sir, your query doesn't work on my machine. I either have to GROUP BY orderID, or drop orderID and Name from the SELECT clause. I'm not sure what you're trying to do, so I don't know which to suggest. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: SQL Problem
I have a problem adapting a multiple select to MySQL. Can someone help me ? Here is my request (Oracle format) SELECT Tactic.teamCode FROM Tactic WHERE Tactic.teamCode NOT IN (SELECT Game.teamCode1 FROM Game, Turn WHERE Game.gameTurn = Turn.code) AND Tactic.teamCode NOT IN (SELECT Game.teamCode2 FROM Game, Turn WHERE Game.gameTurn = Turn.code); I could use the following one, if it's easier to translate to MySQL SELECT Tactic.teamCode FROM Tactic WHERE Tactic.teamCode NOT IN (SELECT Game.teamCode1 FROM Game, Turn WHERE Game.gameTurn = 3) AND Tactic.teamCode NOT IN (SELECT Game.teamCode2 FROM Game, Turn WHERE Game.gameTurn = 3); Thanks Cedric Lefebvre SELECT Tactic.teamCode FROM (Tactic tc LEFT JOIN Game g ON (tc.teamCode = g.teamCode1) OR (tc.teamCode = g.teamCode2)) LEFT JOIN Turn tn ON g.gameTurn = tn.code WHERE (g.teamCode1 IS NULL) AND (g.teamCode2 IS NULL); Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: Problems with sum() in a query.
I'm gaving a problem with adding several sums together when using a group by clause. The table I have looks like this: school_id int school_name varchar sport varchar cost_a float(9,2) cost_b float(9,2) cost_c float(9,2) The real table is normalized, figured it would make for a simpler example this way, so you don't have to tell me ;) Each school has records for several diffrent sports I'm trying to query for the total amount spent for each school with the following query. SELECT school_name, (sum(cost_a)+sum(cost_b)+sum(cost_c)) as total FROM schools GROUP BY school Sir, the obvious error is that school is not a column in your table. Try grouping by school_id. Bob Hall With the desired result school_1 125000.00 school_2 234642.12 school_3 98433.45 But I get zero's for the total column. If I query for only one school I get the correct numbers: SELECT school, (sum(cost_a)+sum(cost_b)+sum(cost_c)) as total FROM schools WHERE school='myschool' GROUP BY school If I query for a few schools (school_id3) sometimes I get unusual numbers, 17.24 for one example. I'm not sure what's happening with these queries so I have to ask. Am I doing the query wrong? Is SQL or mySQL not able to handle this query? Or did I find a bug in mySQL? -- Jeff Bearer, RHCE Webmaster PittsburghLIVE.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 Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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 optimization
Hi, I'm rather new to SQL (well, I had a one-year course at the university many years ago, but they didn't teach us anything practical). I did a few very simple data bases, but now I'm moving to something more serious with joins and 'group by'. The idea is to create a search engine for a battery distributor, specifying various characteristics like * who makes it (only one per battery) * special characteristics (various) * tipical applications (various) To simplify, I have the following tables: Battery: * batID (primary key) * makID * descr Maker: * makID (primary key) * mak A battery can have only one manufacturer, so you might as well add the mak column to Battery and remove Maker. Special: * speID (primary key) * special Bat_Spe: * batID * speID (both in primary key) This is a join table. It creates a many-to-many join between Battery and Special. Tipical: Do you mean Type? * tipID (primary key) * tipical Bat_Tip: * batID * tipID (primary key) From the SELECT statement immediately below, it appears that this is a join table. If that is the case, then Bat_Tip.tipID should not be a primary key. If Bat_Tip.tipID is a key, then you don't need the Bat_Tip join table. * *** First I want to make a list of batteries with all the carateristics they have: SELECT M.mak, B.descr, S.special, T.tipical FROM Battery B, Maker M, Special S, Bat_Spe BS, Tipical T, Bat_Tip BT WHERE M.makID=B.makID AND B.batID=BS.batID AND BS.speID=S.speID AND B.batID=BT.batID AND BT.tipID=T.tipID QUESTION: Is there a better way of writing this join ? Given your current schema, no. * *** Now I want to make a list of batteries which have caracteristics: * Special: speA * Tipical: tipA or tipB Además I want to sort the result such that if a battery has both tipA _and_ tipB, it comes first. I am thinking of doing it with temporary tables: tmp_spe: * batID (primary key) * count tmp_tip: * batID (primary key) * count INSERT into tmp_spe SELECT batID, count(speID) FROM Bat_Spe WHERE (speID='speA') GROUP BY batID QUESTION: Is there any difference between * SELECT batID, count(speID) * SELECT batID, count(batID) * SELECT batID, count(*) ? In this case, the three work and return the same result. In the future, if you decide to change the WHERE clause to search for two or more speIDs (e.g. WHERE speID = 'speA' OR speID = 'speB'), then the counts will be different. Decide what you actually want to count; speIDs, batteries, or rows. Even if the WHERE clause doesn't change, what you count gives you a clue as to what the statement does. Suppose you want to count rows and you use count(batID). Six months from now when you've forgotten how this works, the use of count(batID) will make it more difficult to figure out that you are counting rows. INSERT into tmp_tip SELECT batID, count(tipID) FROM Bat_Tip WHERE (tipID='tipA' OR tipID='tipB') GROUP BY batID SELECT B.batID, (TS.count+TT.count) FROM Battery B, tmp_spe TS, tmp_tip TT WHERE B.batID=TS.batID AND B.batID=TT.batID ORDER BY 2 DESC I believe that's a fluke. Since you are ordering by (TS.count + TT.count), a battery with speA and tipA can appear ahead of a battery with tipA and tipB. In order to avoid this, use ORDER BY TT.count DESC You can combine the two ORDER BY conditions to order within TT.count. ORDER BY 2 DESC, TT.count DESC This returns all the batteries which have all the characteristics I want, ordered as I want. QUESTION: Well, how does it sound ? Is there a better way to do it ? Your method looks good to me. Pretty impressive for someone who studied SQL many years ago for a period of one year. QUESTION: Now, I would need to get, for each battery matched, a list of all the characteristics it has. That is, if battery with batID='bat1' has tipical applications 'tipA', 'tipB' and 'tipC', can I retrieve in one shot all the batteries matched, together with the characteristics each one has, _or_ do I have to go through more temporary tables, _or_ is it better to make one query for each battery I have Join the last statement above to the Bat_Tip and Tipical tables. SELECT B.batID, T.tipical FROM Battery B, tmp_spe TS, tmp_tip TT, Bat_Tip BT, Tipical T WHERE B.batID=TS.batID AND B.batID=TT.batID AND B.batID = BT.batID AND BT.tipID = T.tipID You can join to the Special table in the same way. Well, I know that's a bit long, but it would greatly help to orientate me on how to continue... Thanks in advance for any help or hint, Olivier All SQL was processed in the MySQL server in my brain, not the one on my computer. Expect syntax errors. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - Before posting
Re: Large scale statistical analysis
of Unix, you can set up a cron job to do this automatically. Consider ways of splitting up your tables. If most of the searches will be made on certain groups of objects, separate those objects and their measurements out and put them in their own tables. When you need to search across all objects, you can run several queries and combine the results in temp tables. Or if 10% of your objects are the subject of 90% of your SELECT statements, you could copy those 10% and their measurements to separate tables and make them available for quick searches, with the full database still available for slower searches. If you haven't done so already, put as much memory into your server box as it will hold. That will be $0.02, please. If my advice is no good, I'll refund the money. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: recursive select
Dear, I have an table which like to use recursive join but I wonder how to use it. Please see the table struc below. emp.id,emp name, emp salary, emp head-id 001,mr. a,1,001 002,mr. b,5000,001 003,mr. c,5000,003 004,mr. d,5000,003 Where emp. head-id is id from emp.id. If I'd like to know which head count name of id 002 (it must shown name from id 001). How could I do with mysql? Regards, Sommai Fongnamthip Sir, you are trying to deal with a tree structure. I'm going to do something I've never done before and recommend that you read Joe Celko's book 'SQL For Smarties', which contains two chapters on dealing with tree structures. The table above uses the adjacency list model. The following is modified from Celko's book and returns all boss/subordinate pairs: SELECT e1.name, 'is the boss of', e2.name FROM emp e1, emp e2 WHERE e1.id = e2.head_id; To return the boss 2 levels up, use SELECT e1.name, 'is the boss of the boss of', e3.name FROM emp e1, emp e2, emp e3 WHERE e1.id = e2.head_id AND e2.id = e3.head_id; Good luck Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: group by and order by
I have a table with texts, all of different type, I have field named type to know which is which. What I would want is with one SQL query select the latest from each type, but this query... select id, headline, type from texts group by type order by date; will give the first of each type. I would like to specify order by before group by, but that won't work. Is there a way to list the latest of each type? thanks for any answers... -- Sandman[.net] Sir, try SELECT d1.id, d1.headline, d1.type FROM your_table d1, your_table d2 WHERE d1.id = d2.id GROUP BY d1.id, d1.date HAVING Max(d1.date) = Max(d2.date); I went to my website at http://users.starpower.net/rjhalljr (click on MySQL on the side bar) and copied this. The only changes were plugging in your column names. There an explanation of how this works, but I have to admit it's not one of my better pieces of expository writing. I should rewrite it, but I'm unemployed and job hunting, working on a web page that describes how to deal with tree structures in MySQL, and finishing up an Excel app that creates regression models of programmers' salaries, so god knows when I'll get around to the rewrite. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: INNER JOIN problem
Is there a limit to the number of inner join statements within an sql statement. I am using the following statement: SELECT DISTINCT suburb_def.Suburb, church_location.Name, church_location.URL FROM (suburb_def INNER JOIN church_location ON suburb_def.ID = church_location.Suburb) INNER JOIN (neighbour_def INNER JOIN suburb_map ON neighbour_def.ID = suburb_map.Neighbour) ON suburb_def.ID = suburb_map.Node WHERE ((suburb_map.Node) = $suburb) ORDER BY suburb_def.Suburb, church_location.Name; This seems to return an invalid result set when called from a php script Regards, Tim Lokot Sir, you can't use a nested join between INNER JOIN and ON. The only thing that can go between INNER JOIN and ON is a table name. With inner joins, it doesn't matter what order the tables on joined in. Rewrite your FROM clause so that you always have 'INNER JOIN table name ON'. Or you can replace the INNNER JOINs with commas and move the ON conditions to the WHERE clause. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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 query gone awry
Greetings. I have two tables that are identical in structure, but each contains data for different years. Here is the desc of the first table: mysql desc counts2000; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | version | decimal(6,3)| YES | | NULL| | | sequence| tinyint(4) | YES | | NULL| | | dt_year | bigint(4) | YES | | NULL| | | dt_month| bigint(2) | YES | | NULL| | | dt_mon_name | varchar(10) binary | YES | | NULL| | | dt_day | bigint(2) unsigned zerofill | YES | | NULL| | | date_taken | date| YES | | NULL| | | exams | bigint(21) | | | 0 | | | dt_label| varchar(6) | YES | | NULL| | +-+-+--+-+-+---+ 9 rows in set (0.03 sec) The desc for the other is included at the end of the message. This first one has data for Jan/2000 - Jun/2000. The second has data for Jan/2001 - Apr/2001. I want to combine the two tables into one new one. Since they have a differing number of rows, but I want all of the rows from counts2000, I thought to use a JOIN. Here is the code I am trying to use: code starts here -- DROP TABLE IF EXISTS counts; CREATE TABLE counts SELECT AL1.sequence, AL1.dt_month, AL1.dt_day, AL1.dt_label, SUM(AL1.exams) AS y2000, SUM(AL2.exams) AS y2001 FROM counts2000 AL1 LEFT JOIN counts2001 AL2 USING (sequence,dt_label) WHERE AL1.version = '2' AND AL2.version = '2' GROUP BY AL1.sequence, AL1.dt_label ORDER BY AL1.sequence, AL1.dt_month, AL1.dt_day; ALTER TABLE counts ADD INDEX sequence (sequence); ALTER TABLE counts ADD INDEX dt_month (dt_month); ALTER TABLE counts ADD INDEX dt_day (dt_day); code ends here -- Seems like it should work. It does run, but the goal behind using the JOIN (to get all of the rows in counts2000) is not being met. I only gets rows from both tables where the row exists in both tables. I am running MySQL 3.23.34 (for Windows) on Windows 2000 Professional. Here is some of the data from counts2000 that is not showing up in the resultant table: 2.000 1 20005 May 01 2000-05-01 154 01-May 2.000 1 20005 May 02 2000-05-02 283 02-May 2.000 1 20005 May 03 2000-05-03 373 03-May 2.000 1 20005 May 04 2000-05-04 298 04-May 2.000 1 20005 May 05 2000-05-05 160 05-May 2.000 1 20005 May 06 2000-05-06 117 06-May 2.000 1 20005 May 07 2000-05-07 44 07-May 2.000 1 20005 May 08 2000-05-08 308 08-May 2.000 1 20005 May 09 2000-05-09 408 09-May Any ideas why the JOIN does not seem to be working? Thanks, Dan SUPPORTING DOCS --- mysql desc counts2001; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | version | decimal(6,3)| YES | | NULL| | | sequence| tinyint(4) | YES | | NULL| | | dt_year | bigint(4) | YES | | NULL| | | dt_month| bigint(2) | YES | | NULL| | | dt_mon_name | varchar(10) binary | YES | | NULL| | | dt_day | bigint(2) unsigned zerofill | YES | | NULL| | | date_taken | date| YES | | NULL| | | exams | bigint(21) | | | 0 | | | dt_label| varchar(6) | YES | | NULL| | +-+-+--+-+-+---+ 9 rows in set (0.03 sec) Dan Huston Sir, the join is working fine. That's the way it's supposed to work. If the two tables have identical structure, then use a MERGE table to combine them, and select from the MERGE table. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - Before posting, please check
Re: Agregrate in joined table result
I have this tables : mysqlselect * from bid_history; +---++-+---+--+-+ | domain_id | bid_time | user_id | price | bid_type | proxy_price | +---++-+---+--+-+ | 1 | 20010501035942 | 2 | 10.00 |1 | 10.00 | | 1 | 20010501035952 | 3 | 10.00 |1 | 10.00 | | 1 | 20010501035956 | 4 | 10.00 |1 | 10.00 | | 1 | 20010501040628 | 5 | 11.00 |1 | 11.00 | | 2 | 20010501052520 | 2 | 10.00 |1 | 10.00 | | 2 | 20010501052529 | 3 | 10.00 |1 | 10.00 | | 1 | 20010501052542 | 2 | 11.00 |1 | 11.00 | | 2 | 20010501052655 | 2 | 12.00 |1 | 12.00 | | 1 | 20010501052542 | 3 | 11.00 |1 | 11.00 | +---++-+---+--+-+ mysql select domain_id , domain_name from domain; +---+-+ | domain_id | domain_name | +---+-+ | 1 | iwan| +---+-+ I have doing this query (but the results don't satisfied me) : mysqlselect b.domain_id, b.user_id , max(b.price) as mb , b.bid_time , a.domain_name , a.minimum_price from domainplayer_bid_history as b , domainplayer_domain as a where a.domain_id = b.domain_id group by b.domain_id , b.user_id , b.bid_time order by b.domain_id , mb desc , b.bid_time asc ; +---+-+---++-+-- -+ | domain_id | user_id | mb| bid_time | domain_name | minimum_price | +---+-+---++-+-- -+ | 1 | 5 | 11.00 | 20010501040628 | iwan| 10.00 | | 1 | 2 | 11.00 | 20010501052542 | iwan| 10.00 | | 1 | 3 | 11.00 | 20010501052542 | iwan| 10.00 | | 1 | 2 | 10.00 | 20010501035942 | iwan| 10.00 | | 1 | 3 | 10.00 | 20010501035952 | iwan| 10.00 | | 1 | 4 | 10.00 | 20010501035956 | iwan| 10.00 | +---+-+---++-+-- -+ I want the query's result : +---+-+---++-+-- -+ | domain_id | user_id | mb| bid_time | domain_name | minimum_price | +---+-+---++-+-- -+ | 1 | 5 | 11.00 | 20010501040628 | iwan| 10.00 | | 1 | 2 | 11.00 | 20010501052542 | iwan| 10.00 | | 1 | 3 | 11.00 | 20010501052542 | iwan| 10.00 | | 1 | 4 | 10.00 | 20010501035956 | iwan| 10.00 | +---+-+---++-+-- -+ How to do this in mysql ? Sir, don't group on bid_time. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: Problem with multi-part keys
Hello All, I've bumped into a problem and can't figure out how to solve it. If anyone has a spare minute to give me a hint on what's wrong I'd apreciate it a lot! The problem is that when I add 'use index (multi-part index)' to my queries, MySQL doesn't always use the whole key, but decides what left-most part of the key to use itself. Sometimes its decisions are rather strange to me. Here are some tests that show what I mean: CREATE TABLE test3 ( pr mediumint(8) unsigned NOT NULL default '0', a mediumint(8) unsigned default '0', b mediumint(8) unsigned default '0', data mediumint(8) unsigned default '0', KEY a (a), KEY b (b), KEY k (a,b) ); INSERT INTO test3 VALUES (1,0,0,9); INSERT INTO test3 VALUES (2,0,1,9); INSERT INTO test3 VALUES (3,1,0,9); INSERT INTO test3 VALUES (4,1,1,9); INSERT INTO test3 VALUES (5,1,2,9); INSERT INTO test3 VALUES (6,0,0,9); Test 1 == explain select R.data from test3 as R, test3 as L use index (k) where L.a=IF(R.a=0,1,0) and L.bR.b; # The result is OK (just what I expected): +---+---+---+--+-+--+--+ -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+--+-+--+--+ -+ | R | ALL | b | NULL |NULL | NULL |6 | | | L | index | a,b,k | k| 8 | NULL |6 | where used; Using index | +---+---+---+--+-+--+--+ -+ Test 2 == explain select R.data from test3 as R, test3 as L use index (k) where ((L.a=0 and R.a=1) or (L.a=1 and R.a=0)) and L.bR.b; # Why doesn't MySQL use the whole k index (8 bytes)? +---+---+---+--+-+--+--+ -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+--+-+--+--+ -+ | R | ALL | a,b,k | NULL |NULL | NULL |6 | where used | | L | range | a,b,k | k| 4 | NULL |5 | where used; Using index | +---+---+---+--+-+--+--+ -+ Test 3 == explain select R.data from test3 as R, test3 as L use index (k) where L.aR.a and L.bR.b; # k key isn't choosen by MySQL. Why? +---+--+---+--+-+--+--+- -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--+- -+ | R | ALL | a,b,k | NULL |NULL | NULL |6 | | | L | ALL | k | NULL |NULL | NULL |6 | range checked for each record (index map: 4) | +---+--+---+--+-+--+--+- -+ Test 4 == explain select R.data from test3 as R, test3 as L use index (k) where R.a=0 and L.a=0 and L.bR.b; # Why doesn't MySQL use the whole k index (8 bytes)? +---+--+---+--+-+---+--+ -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+--+ -+ | R | ref | a,b,k | a| 4 | const |2 | where used | | L | ref | a,b,k | k| 4 | const |2 | where used; Using index | +---+--+---+--+-+---+--+ -+ The bottom line is that I'd like to know if MySQL has a feature that makes it use a key even if the optimizer doesn't think it'a good one. Thanks in advance. Regards, Alexander Paperno. Sir, your SQL statements are generating cartesian products, which force MySQL to examine all rows of one or both tables. This makes your indices useless, so MySQL doesn't use them. And now for some nitpicking: A KEY is not a key. A KEY in MySQL is a non-unique index that allows nulls. A key contains unique values and doesn't contain nulls. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: An interesting SELECT problem
I've been creating a site for someone using MySQL and PHP4. Basically the table concerned is structured like this; id int(5) UNSIGNED Noauto_increment Primary ship varchar(50) NoIndex year varchar(15) NoIndex Sir, change the type of this column to Date, which stores the date as -mm-dd. This will allow you to order the records by the date. You can display the date in a different format using Date_format(). Using a 15 character varchar field for data that is exclusively dates and requires a maximum of 10 characters is an invitation for trouble. Also, since the column contains the full date of the voyage, change the name of the column from year (it's not the year, it's the date) to something like sail_date or embarked. voyage varchar(50) Yes sex varchar(50)Yes How do you determine the sex of a voyage? And why does it take 50 characters to specify it? :-) notes text Yes Everthing is working fine except the ship order in which the pages are generated. For example, the following are ships names and yes the records do show the voyage date (they are in official records that way and I can change them. I prudently added the year field and the year is put in that field as well. Some ships don't have this date after their name, sometimes they have just a voyage number. Henrietta Henry Henry Porcher Hibernia Hindostan (1) Hindostan (2) Hyderabad (1) Hyderabad (2) Hyderabad (3) Marian Watson (08-06-1842) Marian Watson (28-10-1841) Marion Watson (08-03-1842) Here in lies the problem, these records have been entered at different times so their id's are all over the place When I get these records and display them, they will be in the correct alphanumeric order except for the ones with the date after them. they will only display in the order they were entered. I've tried the following SELECTs $result = mysql_query(SELECT * FROM ships ORDER BY ship,$db) $result = mysql_query(SELECT * FROM ships ORDER BY ship, year,$db) but they dont sort the way we want them to (in year order) as per below Henrietta Henry Henry Porcher Hibernia Hindostan (1) Hindostan (2) Hyderabad (1) Hyderabad (2) Hyderabad (3) Marian Watson (28-10-1841) Marion Watson (08-03-1842) Marian Watson (08-06-1842) Anyone got any ideas? Thanks in advance Howard Picken [EMAIL PROTECTED] Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: Viewing data from the previous row
Actually, there is a way to do this by taking advantage of variables. If you read my previous post on the matter, you can use variables to get a cumulative total. - Original Message - From: Steve Ruby [EMAIL PROTECTED] To: Roger Karnouk [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, April 27, 2001 12:37 PM Subject: Re: Viewing data from the previous row Roger Karnouk wrote: Is it possible in a Select query to get values from the previously read row in order to do things like cumulative sums or other formulas which require the previous value in order to calculate the current value. ex. day current sales cumu_total - --- 1 120 120 2 60 180 3 125305 4 40 345 I'd like to be able to do this in one select statement is this even possible? Roger Karnouk [EMAIL PROTECTED] Sir, the following works on my machine: SELECT t1.period, t2.sales AS current_sales, Sum(t2.sales) AS cum_total FROM table t1, table t2 WHERE t2. period = t1. period GROUP BY t1. period; Have I ever mentioned the book 'SQL For Smarties' by Joe Celko in any of my previous posts in this mailing list? Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: min () - sql troubles
Hi SQL troubles: I have a table containing 4 columns: name - string, distance- double, speed - time and id - AUTO_INCREMENT (Primery key) I want a list of the fastest (speed) runner (name) of each distance and the speed. I use: select name, distance, min(speed) from table1 group by distance; I get a list where the speed and the distyance are always correct but the name is not always. Why is that? What should I do? Thanks, // Chris - Copenhagen Sir, try SELECT t1.name, t1. distance, Min(t1.speed) FROM table1 t1, table1 t2 WHERE p1.name = p2.name GROUP BY t1.distance HAVING Min(t1.speed) = Min(t2.speed); I haven't run this, but I think it's correct. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: SQL Syntax question
Hi there, I'm using mysql 3.22.27 and get error when trying to run this select statement: SELECT custmls.mlsnumber,custmls.streetnumber,custmls.streetdirect, FORMAT(custmls.currentprice,0),custmls.streetnam,custmls.streetaddtl, custmls.municname,custmls.state,custmls.zipcd,custmls.salesassoc, ipix.url,custmls.listagentname,FORMAT(custmls.numrooms,0), FORMAT(custmls.numbedrooms,0),custmls.fullbaths FROM custmls,ipix where custmls.mlsnumber = ipix.mlsnumber AND custmls.listingoffice = 0251 AND (custmls.listingstatus = 'ACT' or custmls.listingstatus = 'A*') AND custmls.scategory = 1 ORDER BY custmls.currentprice The error is: "1064 You have an error in your SQL syntax near 'ON ipix custmls.mlsnumber = ipix.mlsnumber where custmls.listingoffice = 0251 AN' at line 1 " Any ideas? Thanks Pat Sir, the error message was obviously from an SQL statement other than the one you quote above. Since I don't know your table structure, I can't be sure what the problem is, but it looks like you are writing columns and tables as table_name.column_name. This is backwards. It should be column_name.table_name. The alternative is that you are selecting from a boat load of tables that aren't mentioned in the FROM clause. I see two problems with the snippet of SQL quoted in the error message. First of all, the word 'ipix' after ON isn't serving any function, other than to confuse MySQL. Secondly, you are trying to join two tables, but your ON clause joins a column from mlsnumber to another column from mlsnumber. If this is not the error mentioned above, then you need to join a column in mlsnumber to a column in the second table. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: sql: DATETIME - MONTH( select problem)
Hi, I'm having the following sql problem: I have two datetime fields (start and end), I need to find out if the month is either equal to start-month or equal to end-month but also if it's a month between start and end. I also check if the end-date is greater than today so it's a current one. My problem is that I can't get the months in between when, how do I do that? Any ideas? $current_month is a php variable. (MONTH(start)= '$current_month' OR MONTH(end) = '$current_month') AND end = CURDATE() Greetings from Sweden Jimmy. Sir, try (MONTH(start) = $current_month AND MONTH(end) = $current_month) AND end = CURDATE() The MS Titanic (my wintel machine) just sank again, so I don't have a machine I can run the statement on to see if it is correct. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: Outer and left join definition
bob... your example does not appear to work for me. maybe i'm missing something? i created two tables simply as: create table test1 values (id int); create table test2 values (id int); and populated them with some matching and non-matching data. however, when i run the query: select test1.id, test2.id from test1 left join test2 using id; i get the following error: You have an error in your SQL syntax near 'id' at line 1 Sir, it's not a good idea to trust the syntax of anything I've posted unless I specifically say that I've run it on my machine. I'm very absent-minded. In this case I was only trying to provide a more or less generic example of a LEFT JOIN, not a syntactically correct example of a MySQL SELECT statement. By pure luck, the only thing missing is a pair of parentheses. I've just run the following on my machine, and it works. select test1.id, test2.id from test1 left join test2 using (id); On the other hand, the following does not work: create table test1 values (id int); I checked the on-line manual. (Ah ha! The fine manual! We can look in the manual get answers to syntax questions! Why didn't we think of that sooner!?) I can't find anything that suggests that you can use VALUES in a CREATE TABLE statement, so I think you probably used create table test1 (id int); which is what I used. Gee, we solved both problems by using the manual. Dr. Pangloss was right; this really is the best of all possible worlds! (Sorry. It's Sunday morning and I'm feeling really silly.) any ideas? Never. I'm not allowed, according to my last date. She said "Don't get any ideas." It's not working, though. I've been idea-free for weeks, and she still isn't returning my phone calls. Bob Hall Bob Hall wrote: SELECT table_a.id, table_b.id FROM table_a LEFT JOIN table_b USING id; would return something like table_a.id table_b.id __ __ a NULL b b c c d NULL Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: RE - MYSQL recovery/reliability under powerfail conditions
Hi We are evaluating various RDBMS for use on within an airborne server (Embedded NT4.0) environment. This environment does not have reliable power. Aircraft are powered cycled without regard for delicate OS environments etc. Here in south Arlington, Va., the power also cycles without regard for delicate OS environments. Is it impossible to install some sort of UPS, along with software that shuts everything down gracefully when the power goes off? This will also protect against less obvious problems. My dial-up connection is lot more reliable when my equipment is hooked up to a UPS. We like MySql because it is fairly lightweight (regards processor/mem requirements not capabilities) and fast. Availability of source code is also re-assuring - given the long product lifecycles within the avionics business. Assume that we are not suffering from any file corruption due to problems with NTFS under powerfail conditions. Whether we use a table type which supports transactions or not has anyone experienced problems under powerfail, and if they have could it be automatically corrected within a reasonable time by the table repair utilities. I am worried about internal indices (Btrees etc) requiring a complete rebuild, and taking an unacceptable amount of time. Roughly half the crashes I've experienced have resulted in corrupted tables. I've always been able to repair them with the mysamchk utility. I plan to test before making any commitments but forwarned is forarmed as they say. Other DB's on the eval list are Interbase 6.0, MSQL7, various realtime embedded. Thanks Regards Norman Burkies - 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 Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: How update 1 of two identical rows.
Unfortunately I am using 3.22.32. Does this mean I am out of luck with this query. Peter Schwenk wrote: If you are using version 3.23.x of MySQL, there is a LIMIT clause that you can use to just update up to a certain number of records, so you could add 'LIMIT 1' to the UPDATE command to only change one record. Richard Reina wrote: DateDB_amount CR_amount 2001-04-09 NULL300.00 2001-04-09 NULL750.00 2001-04-09 NULL300.00 Anyone know how can I do a query that will update the third record without updating the first? Sir, create a new table containing no duplicates. INSERT INTO new_table SELECT * FROM old_table GROUP BY Date, DB_amount, CR_amount Delete old_table. Change the name of new_table to old_table. And finally, redesign your table to avoid this kind of embarrassment. You don't want to make a habit of deleting tables in order to overcome design problems. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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: Foreign Keys
On Thu, 5 Apr 2001, Dennis Gearon wrote: Date: Thu, 05 Apr 2001 14:24:25 -0700 From: Dennis Gearon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Foreign Keys Are people using Mysql using foreign keys and how are they using them? It seems that the way to use them is with the scripting language used to access the database. bonjour, neither foreign keys nor need for foreign keys using mysql. Use WHERE clause. A foreign key is a column (or columns) in a table that refers to a key in another table. A foreign key contains only values in the referenced key, or NULLs. Joins on referenced key/foreign key pairs are very common, regardless whether the DBMS is MySQL, Oracle or what-have-you. What MySQL lacks, that many DBMSs have, are relational integrity constraints that prevent you from inserting or altering values in a foreign key that are not in the referenced key, or deleting the rows in the referenced table that the foreign key refers to. This means that the programmer has to be careful to avoid writing code that creates orphan records, since MySQL won't prevent you from creating them. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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: date select question (better explained)
I'm trying to figure out how to select records from an events database which (from today) will take place on next weekend. The table has a from_date and a to_date fields, among other. I've found a way mixing PHP with SQL, but I'm curious to know if is there a neat and pure SQL version using the built-in date functions in mySQL. Can you explain differently or give some more detail? Perhaps show some actual table data, show what output you want, state explicitly in words what you want to accomplish and explain what the problem is. I'm sure I (and any number of others can help), but I don't understand the problem based on what you've written. Sorry, I'll try to explain better. I want to develop a php/mysql based agenda of city activities, and these are stored in a table with id,type,title,description and from_date and to_date fields. A pseudo query which would retrieve what I need would be: select * from city_agenda where from_date='next_sunday' and to_date='next_saturday'; But I do not see how to get 'nex_saturday' and 'next_sunday' using mySQL date functions. With PHP is easy to find out these dates and pass them to the select, but I wonder if is there a pure SQL version of this. Anyone? TIA Llorenc Sole Maresme Netcom, S.L. Sir, the following retrieves the date for the Sunday of the current week that runs from Sunday to Saturday. Use Data_add() to add 7 and 13 days for next Sunday and the following Saturday. SELECT Date_sub( Now(), Interval If(Weekday(Now()) = 6, 0, Weekday(Now()) + 1) day ) Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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: Multi-level JOIN query
Hi! I need some suggestions about a special query. The situation is: There are (for simplicity) 4 tables: A,B,C and D which must be joined like this: Between A and B must be an outer-join association and C and D must be connected to B also with outer-join. But this causes a 'Cross dependency found in OUTER JOIN' error. I could connect A and B with INNER JOIN but it's useless from my point of view. I think the problem could be solved with redesigning the database structure, but it's currently not possible. You need to specify the outer join. A LEFT OUTER JOIN B is not the same as A RIGHT OUTER JOIN B or B LEFT OUTER JOIN A. When you have nested outer joins, only one of the tables can be preserved. You need to specify which table is being preserved (all rows are returned). I recommend posting the table definitions and stating which columns are used in the joins. Does somebody know a good (general) solution for this? Temporary tables could help, but i deal with lot's of data and I'm afraid it would be very slow. Thanks! Christian Fischer Outer joins + lot's of data = slow queries, even without temp tables. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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: Calculations in a field?
"Bob Hall" [EMAIL PROTECTED] wrote: Quorting someone else who's name is no longer present: Is there any way to make one field in a table calculate another field. I'm making a DB that calculates commissions. So in the money_made field would have the # of sales (sales field) times whatever the commission would be. Yes sir, but don't do it. That's the classic example of something that should not be in a table. Calculated fields belong in queries. In a lot of cases that's true to prevent storing of redundant data. However, the poster's example was about sales and commissions. In the real world commission rates within a company can change over time so if the poster is trying to calculate the commission in conjunction with an INSERT STATEMENT it might be wise to have the commission rate in a variable in the associatd application and add the commission as the record is entered. Then when the commission rate goes down (or up), a variable can be modified and the application will work fine. If the commission rate is already in the table (the poster didn't state this, but it could be) then it might make sense to calculate the commission instead of saving it in a field. Sir, it doesn't matter whether the commission rate is stored in the table or the application. In either case, the application can generate an SQL statement that calculates the commission. Commission rates can be changed either in the client application or in the table. Then again, if the number of rows is large and reports are going to be generated based on things like total commissions by sales person by month, it might create more overhead than desired. If the reports are generated monthly or less often, then the overhead isn't going to matter much, unless you have a huge organization with thousands of sales people, and the query that calculates the commission is very complex. I have had to include calculated fields in tables occasionally, but only when the SQL optimizer was confused by several layers of subqueries. For obvious reasons, this has never happened with MySQL. -- Steve Werby President, Befriend Internet Services LLC http://www.befriend.com/ Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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: Mysqld crashes with my DELETE query.
Bonjour, I have a table created with : CREATE TABLE intraday_PAR ( symbole char(20) NOT NULL default '', date datetime NOT NULL default '-00-00 00:00:00', Don't use reserved words for column names. first double(17,5) NOT NULL default '0.0', high double(17,5) NOT NULL default '0.0', low double(17,5) NOT NULL default '0.0', last double(17,5) NOT NULL default '0.0', volume int(11) NOT NULL default '0', PRIMARY KEY (symbole,date), KEY idx_symbole (symbole), KEY idx_date (date) ) TYPE=Innobase; There are about 400.000 rows in it : mysql select count(*) from intraday_PAR; +--+ | count(*) | +--+ | 379568 | +--+ 1 row in set (0.86 sec) If I try to do a delete based on "date" (for which there is an index), it is really really long : | 5 | root | localhost | cotations | Query | 255 | updating | delete from intraday_PAR where date'2001-04-03 00:00:00' Before mysqld crashes : mysql delete from intraday_PAR where date'2001-04-03 00:00:00'; ERROR 2013: Lost connection to MySQL server during query And from the error log : /usr/local/mysql/libexec/mysqld: ready for connections mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. 010403 12:32:19 mysqld restarted Innobase: Database was not shut down normally. Innobase: Starting recovery from log files... Innobase: Starting log scan based on checkpoint at Innobase: log sequence number 0 487424247 Innobase: Doing recovery: scanned up to log sequence number 0 487489536 Innobase: Doing recovery: scanned up to log sequence number 0 487555072 Innobase: Doing recovery: scanned up to log sequence number 0 487600404 Innobase: 1 uncommitted transaction(s) which must be rolled back Innobase: Starting rollback of uncommitted transactions Innobase: Rolling back trx no 4045747 Innobase: Rolling back of trx no 4045747 completed Innobase: Rollback of uncommitted transactions completed Innobase: Starting an apply batch of log records to the database... Innobase: Apply batch completed Innobase: Started /usr/local/mysql/libexec/mysqld: ready for connections Regards, Alex. - 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 Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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: Calculations in a field?
Is there any way to make one field in a table calculate another field. I'm making a DB that calculates commissions. So in the money_made field would have the # of sales (sales field) times whatever the commission would be. Yes sir, but don't do it. That's the classic example of something that should not be in a table. Calculated fields belong in queries. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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 updating a subsection of a field possible?
Sir, use the Replace function. Regex isn't necessary. I've got a table with 500+ records in it now... and I need to update just a small section of one of the fields (an URL) to change a parameter on it... I know I'm probably going to be told "NO", but I thought I'd ask before I go start writing a program to do this Is it possible to update just a small subsection of a field automagically via SQL and the mysql query language extensions? I'd need some sort of regex intervention, such that the text that surrounds the text I need to change isnt affected - 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 Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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: Can't recover a bad corrupeted table...
Hello Bob, first at all thank you for your support. Bob Hall wrote: Let me see if I understand you correctly. You created a new table (CREATE TABLE?) and you imported data from a file that had nothing to do with MySQL (comma or tab delimited file?), and therefore couldn't be affected by whatever crashed your server. And this brand new table with pristine data was corrupted? Yep this is whats happen. I first i got a corrupted table, i tried to repair the table following all known strategies... then i create a table from scratch and use a perl program for importing data, but at the end i got a corrupted table and no way to rapair it. This is the schema: 1) use queries drop table keywords; create table keywords (querystring varchar(255), numref int(11), multiword smallin t(6), date date, origine varchar(16)); The use of a reserved word as a column name probably is the reason that your table is corrupted as soon as it is created. Since your server is working normally, and this particular table and only this particular table was corrupted ab ovo, then the problem seems to be specific to the table. In general, use of a reserved word as a column name is definitely a problem. create FULLTEXT INDEX qfindex ON keywords(querystring); create INDEX qindex ON keywords(querystring); create INDEX orindex ON keywords(origine); 2) Importing data with perl program 3) Corrupted table I have limited experience maintaining MySQL databases on Linux, and I have very limited knowledge of the internal workings of MySQL. The only thing that I know of that you haven't mentioned trying is recreating the table description file from backup. However, if CREATE TABLE statements are creating corrupted tables, then I believe that the server itself is corrupted. If I were in your shoes, I would reinstall, after backing up everything short of the refrigerator. Perhaps someone else can give you better advice. The strange thing is that this is a production server used by a lot of programs and they works. Now i'm tring to use ISAM (instead of MYISAM) table with such schema: use queries drop table keywords; create table keywords (querystring varchar(255) NOT NULL, numref int(11), multiwor d smallint(6), date date, origine varchar(16) NOT NULL) TYPE = ISAM; create INDEX qindex ON keywords(querystring); create INDEX orindex ON keywords(origine); I don't know if this work at the moment it is importing... Notice a strange thing, ISAM ask for not null definition of indexed fields, MYISAM not .. why ? Bob Hall I tried -r I tried -o I tried to truncate the table and re-build the index from scratch. I tried to create ex-novo a table, import data from scratch and it result in a corrupted table. None of these seems to work Bob Hall wrote: Sir, I looked quickly through the mass of data supplied below, and it looks like you only tried m with the -r -q option combination. Try it with just -r. If that doesn't work, try it with -o. If that doesn't work, restore from backup. Got backup? Bob Hall Antonio gulli wrote: Any help is appreciated Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 158 to server version: 3.23.36-log myisamchk -V myisamchk Ver 1.45 for pc-linux-gnu at i686 a) Trying a recovery. myisamchk -r -q -Osort_key_blocks=16 keywords - check key delete-chain - check record delete-chain - recovering (with keycache) MyISAM-table 'keywords.MYI' Data records: 2062985 7000 [root@ideare queries]# myisamchk -r -q -Osort_key_blocks=16 -Okey_buffer_size=256M -Oread_buffer_size=256M -Osort_buffer_size=256M keywords - check key delete-chain - check record delete-chain - recovering (with keycache) MyISAM-table 'keywords.MYI' Data records: 2062985 - - check key delete-chain - check record delete-chain - recovering (with keycache) MyISAM-table 'keywords.MYI' Data records: 2062985 b) Trying a check Database changed mysql CHECK TABLE keywords; +--+---+--+--- -+| Table| Op| Msg_type | Msg_text |+--+---+--+-- --+| keywords | check | error| Key in wrong position at page 24942592 || queries.keywords | check | error| Corrupt |+--+---+--+-- --+2 rows in set (10.12 sec) yisamchk -a -Osort_key_blocks=16 -Okey_buffer_size=256M -Oread_buffer_size=256M -Osort_buffer_size=256M keywords \Checking MyISAM file: keywords Data records: 2062985 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check data record
Re: Can't recover a bad corrupeted table...
Let me see if I understand you correctly. You created a new table (CREATE TABLE?) and you imported data from a file that had nothing to do with MySQL (comma or tab delimited file?), and therefore couldn't be affected by whatever crashed your server. And this brand new table with pristine data was corrupted? I have limited experience maintaining MySQL databases on Linux, and I have very limited knowledge of the internal workings of MySQL. The only thing that I know of that you haven't mentioned trying is recreating the table description file from backup. However, if CREATE TABLE statements are creating corrupted tables, then I believe that the server itself is corrupted. If I were in your shoes, I would reinstall, after backing up everything short of the refrigerator. Perhaps someone else can give you better advice. Bob Hall I tried -r I tried -o I tried to truncate the table and re-build the index from scratch. I tried to create ex-novo a table, import data from scratch and it result in a corrupted table. None of these seems to work Bob Hall wrote: Sir, I looked quickly through the mass of data supplied below, and it looks like you only tried m with the -r -q option combination. Try it with just -r. If that doesn't work, try it with -o. If that doesn't work, restore from backup. Got backup? Bob Hall Antonio gulli wrote: Any help is appreciated Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 158 to server version: 3.23.36-log myisamchk -V myisamchk Ver 1.45 for pc-linux-gnu at i686 a) Trying a recovery. myisamchk -r -q -Osort_key_blocks=16 keywords - check key delete-chain - check record delete-chain - recovering (with keycache) MyISAM-table 'keywords.MYI' Data records: 2062985 7000 [root@ideare queries]# myisamchk -r -q -Osort_key_blocks=16 -Okey_buffer_size=256M -Oread_buffer_size=256M -Osort_buffer_size=256M keywords - check key delete-chain - check record delete-chain - recovering (with keycache) MyISAM-table 'keywords.MYI' Data records: 2062985 - - check key delete-chain - check record delete-chain - recovering (with keycache) MyISAM-table 'keywords.MYI' Data records: 2062985 b) Trying a check Database changed mysql CHECK TABLE keywords; +--+---+--+--- -+| Table| Op| Msg_type | Msg_text |+--+---+--+-- --+| keywords | check | error| Key in wrong position at page 24942592 || queries.keywords | check | error| Corrupt |+--+---+--+-- --+2 rows in set (10.12 sec) yisamchk -a -Osort_key_blocks=16 -Okey_buffer_size=256M -Oread_buffer_size=256M -Osort_buffer_size=256M keywords \Checking MyISAM file: keywords Data records: 2062985 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 - check data record references index: 3 myisamchk: error: Key in wrong position at page 24942592 - check record links myisamchk: error: Keypointers and record positions doesn't match MyISAM-table 'keywords' is corrupted 3) Describe this table ... myisamchk -dvv keywords MyISAM file: keywords Record format: Packed Character set: latin1 (8) File-version:1 Creation time: 2001-03-28 10:02:22 Recover time:2001-03-29 11:01:02 Status: crashed Data records: 2062985 Deleted blocks: 0 Datafile parts:2062985 Deleted data: 0 Datafile pointer (bytes):4 Keyfile pointer (bytes):4 Datafile length: 79274620 Keyfile length: 99454976 Max datafile length:4294967294 Max keyfile length: 4398046510079 Recordlength: 281 table description: Key Start Len Index Type Rec/key Root Blocksize 1 5 254 fulltext varchar packed 0 26022912 2048 1 4 float 0 2 266 16 multip. char packed stripped NULL 0 56983552 1024 3 2 255 multip. char packed stripped NULL 0 30499840 2048 Field Start Length Nullpos Nullbit Type 1 1 1 2 2 2551 1 no endspace 3 257 4 1 2 no zeros 4 261 2 1 4 no zeros 5 263 3 1 8 no zeros 6 266 16 1 16 no endspace 4) Re-check
Re: Can't recover a bad corrupeted table...
Sir, I looked quickly through the mass of data supplied below, and it looks like you only tried myisamchk with the -r -q option combination. Try it with just -r. If that doesn't work, try it with -o. If that doesn't work, restore from backup. Got backup? Bob Hall Antonio gulli wrote: Any help is appreciated Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 158 to server version: 3.23.36-log myisamchk -V myisamchk Ver 1.45 for pc-linux-gnu at i686 a) Trying a recovery. myisamchk -r -q -Osort_key_blocks=16 keywords - check key delete-chain - check record delete-chain - recovering (with keycache) MyISAM-table 'keywords.MYI' Data records: 2062985 7000 [root@ideare queries]# myisamchk -r -q -Osort_key_blocks=16 -Okey_buffer_size=256M -Oread_buffer_size=256M -Osort_buffer_size=256M keywords - check key delete-chain - check record delete-chain - recovering (with keycache) MyISAM-table 'keywords.MYI' Data records: 2062985 - - check key delete-chain - check record delete-chain - recovering (with keycache) MyISAM-table 'keywords.MYI' Data records: 2062985 b) Trying a check Database changed mysql CHECK TABLE keywords; +--+---+--+--- -+| Table| Op| Msg_type | Msg_text |+--+---+--+-- --+| keywords | check | error| Key in wrong position at page 24942592 || queries.keywords | check | error| Corrupt |+--+---+--+-- --+2 rows in set (10.12 sec) yisamchk -a -Osort_key_blocks=16 -Okey_buffer_size=256M -Oread_buffer_size=256M -Osort_buffer_size=256M keywords \Checking MyISAM file: keywords Data records: 2062985 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 - check data record references index: 3 myisamchk: error: Key in wrong position at page 24942592 - check record links myisamchk: error: Keypointers and record positions doesn't match MyISAM-table 'keywords' is corrupted 3) Describe this table ... myisamchk -dvv keywords MyISAM file: keywords Record format: Packed Character set: latin1 (8) File-version:1 Creation time: 2001-03-28 10:02:22 Recover time:2001-03-29 11:01:02 Status: crashed Data records: 2062985 Deleted blocks: 0 Datafile parts:2062985 Deleted data: 0 Datafile pointer (bytes):4 Keyfile pointer (bytes):4 Datafile length: 79274620 Keyfile length: 99454976 Max datafile length:4294967294 Max keyfile length: 4398046510079 Recordlength: 281 table description: Key Start Len Index Type Rec/key Root Blocksize 1 5 254 fulltext varchar packed 0 26022912 2048 1 4 float 0 2 266 16 multip. char packed stripped NULL 0 56983552 1024 3 2 255 multip. char packed stripped NULL 0 30499840 2048 Field Start Length Nullpos Nullbit Type 1 1 1 2 2 2551 1 no endspace 3 257 4 1 2 no zeros 4 261 2 1 4 no zeros 5 263 3 1 8 no zeros 6 266 16 1 16 no endspace 4) Re-check it mysql CHECK TABLE keywords; +--+---+--+--- -+| Table| Op| Msg_type | Msg_text |+--+---+--+-- --+| keywords | check | warning | Table is marked as crashed || keywords | check | error| Key in wrong position at page 74375168 || queries.keywords | check | error| Corrupt |+--+---+--+-- --+3 rows in set (9.12 sec) 5) http://www.mysql.com/doc/R/e/Repair.html Stage 3: Difficult repair francesca mysql queries mysql SET AUTOCOMMIT=1; mysql TRUNCATE TABLE keywords; mysql quit myisamchk -r -q -Osort_key_blocks=16 -Okey_buffer_size=256M -Oread_buffer_size=256M -Osort_buffer_size=256M keywords - check key delete-chain - check record delete-chain - recovering (with keycache) MyISAM-table 'keywords.MYI' Data records: 0 mysql use queries
Re: GROUP BY Question (p2)
Sir, try SELECT g1.name, g1.score, g1.id FROM grades g1, grades g2 WHERE g1.name = g2.name GROUP BY g1.name, g1.score HAVING Max(g1.score) = Max(g2.score); Bob Hall Q: I have the following table "grades". ++++ | name | score |id | ++++ | John |10 | 1 | ++++ | John | 20 |2 | ++++ | John | 8 |3 | ++++ | Carl |10 |4 | ++++ | Carl |15 |5 | ++++ | Mary |6 |6 | ++++ I'd like to select a single name, maximum score, as well as the corresponding id number and put them in order from highest to lowest and end up with: ++++ | name | score |id | ++++ | John |20 | 2 | ++++ | Carl |15 |5 | ++++ | Mary |6 |6 | ++++ I tried using: SELECT name, id, max(score) FROM grades GROUP BY name ORDER BY score DESC At first I was pretty happy with this, but I realized that although the name and score where the values I was looking for, the id didn't always correspond to the record I wanted. Any ideas - 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 Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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: How do i put integrity constraints?
How do i put in an integrity constraint? ie. say i have 2 tables Person (PersonID[pk], PersonName) School (SchoolID[pk], SchoolName, PersonID[fk]) as you can see PersonID is the foreign key in School. So how do keep update and delete restricted constraints etc,? could anyone tell me the SQL code for bot the tables? duke. Sir, look up REFERENCES in the documentation. But the documentation points out that this is for compatibility with other DBMSs, and doesn't actually do anything. MySQL doesn't enforce integrity constraints on foreign keys. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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: View a return of Select
I have to check to MySQL is able to support our requirements and I would like to view a return of n columns like only one register, thats is posible with MySQL? For example i have 2 tables , 1 table have products and this table habe 3 colums ID , name, Id_price and the other table (prices) have 2 columns Id_price , price , can i return for the 'x' product their prices like price1,price2,...priceN __ Un Saludo Jaume Rubio Depto. Informtica [EMAIL PROTECTED] http://www.epel-ind.com __ Yes sir, you can return the data in that format, but whether it's a good idea is another question. SQL was intended to return data in the format x, price1 x, price2 x, price3 With most RDBMSs, including MySQL, it is possible to force the data into your format, but it is complicated and slows things down. You would be better off having your query return data in the usual format and then have a client program parse the output and reformat it. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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 COUNT()...
Hi,
I have these 3 tables which hold order information
for a web site.
One table holds the shipping info/credit cart stuff,
another one holds one type of item ('home accents'),
and the other one holds house plans.
When someone orders these items, due to the options on
house plans, I must store the order information for
plans and accents in 2 seperate tables. So, I broke
the order info down into the 3 tables described above.
Each of the accentorders and planorders tables have
an order_id field which coresponds to the ID field
in the mainorder table (which holds the shipping and
CC info).
All this works fine, however I'm stumped on one issue.
I want to use one select statement and get all the items
for that one order AND figure out how many 'Plans' that
order has and how many 'Accents' the order has.
I've tried this select statement:
SELECT orders.id,orders.ship_co_name,orders.ship_name,orders.order_date,
orders.order_total,orders.order_complete,orders.bad,
COUNT(DISTINCT planordermain.id) AS plans,COUNT(DISTINCT
accentorderdetail.id) AS accents
FROM orders,accentorderdetail,planordermain
WHERE ((orders.id=accentorderdetail.id) AND
(orders.id=planordermain.order_id))
GROUP BY orders.id
ORDER BY orders.order_date ASC;
Which gives:
++--+++-+
+-+---+-+
| id | ship_co_name | ship_name | order_date | order_total |
order_complete | bad | plans | accents |
++--+++-+
+-+---+-+
| 1 | Mindbender | Johnny Withers | 3/20/2001 | 1426.85 |
0 | 1 | 2 | 1 |
| 2 | Mindbender | Johnny Withers | 3/21/2001 | 1538.13 |
0 | 1 | 2 | 1 |
| 3 | Mindbender | John Doe | 3/21/2001 | 2084.13 |
0 | 1 | 3 | 1 |
++--+++-+
+-+---+-+
It gives correct results for 'plans' but incorrect results for accents.
Also, if a user doesn't order any 'plans' but orders 'accents' this query
will not return any results at all (because of the AND in the where).
I tried to use OR, but then I get (orders*plans_ordered*accent_orders) which
is not right either.
In other words, you want to preserve the orders table, which means an
outer join.
FROM (orders o LEFT JOIN accentordetail a ON o.id = a.id)
LEFT JOIN planordermain p ON a.id = p.id
This replaces both your FROM and WHERE clauses. (If you use the
aliases you'll have to use them in the rest of the SQL statement
also.)
At first blush, the Count() problem looks like a data problem. Being
lazy, I'm not inclined to set up a database and try to duplicate the
problem when I don't see a problem with the SQL. I'm not saying the
problem isn't in the SQL, I'm just saying that I'm lazy. If you
posted the data in the tables that resulted in the output above, I'd
be more inclined to proceed to the next step. In general, it makes it
easier for people on the list to help you if we have both the input
and the output.
It's possible that the outer joins will solve the Count() problem,
but I can't tell without the input data.
Thanks for the input.
-
Johnny Withers
[EMAIL PROTECTED]
p. 601.853.0211
c. 601.954.9133
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
-
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 help
Hi, This is my first posting, although i've been signed up to the list for a while. My problem is this. Table A (5000 rows) ID, NAME, SCORE Table B (1000 rows) ID, NAME, SCORE I want all records from Table A and those from Table B where they match, for this i'm using a right join. However, there are rows in Table B which don't match any in Table A, but i need to include these as well. Any help would be appreciated. Ben. Sir, I haven't seen an answer to your question, so here's one way of getting what you want. What you want is basically the union of three groups of rows: the rows from A and B that match, the rows from A that don't match B, and the rows from B that don't match A. Your outer join returns the first two groups. The last group is returned by a difference query: the rows in B that don't match any of the rows in A (B - A). Since MySQL doesn't yet support UNION, you will have to load the result tables from both queries into another table, and then SELECT * from that table. I have a description of the standard difference query on my website; http:/users.starpower.net/rjhalljr, click on MySQL on the sidebar, click on SQL, and look for the difference query topic. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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: upper and ucase
Sir, if you look through the documentation on the PHP site, you will
find the strtoupper function, and a number of PHP mail lists where
PHP related questions can be posted. If you have any MySQL related
questions, the contributors to this MySQL mail list will be happy to
try and help you.
Bob Hall
Hi,
I am using mysql-3.22.32 with php4.
I have a little problem with the uppercase.
I wanted to display a user input field in CAPS.
This is what I did and it did not work.
$userfield = upper('$userfield');
echo "User Field - $userfield";
Errors (tried both ucase and upper)
Call to undefined function: upper()
Call to undefined function: ucase()
Really appreciate any help that I can get.
***
"Don't be afraid to ask dumb questions, they are a lot easier to
handle than dumb mistakes."
Email : [EMAIL PROTECTED]
Home Page : http://www.familyhoo.net
***
-
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
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
-
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: May I ask for your assistance?
Dear Sir or Madam, Re: Installation MySQL version 3.23.22-beta. I am awfully sorry to bother you with some basics. I am a novice. I am trying to use the above on my PC which is running on Windows 98. In WinMySQLAdmin, my.ini set up appears complete. I have looked at the starting tutorial in the manual which I downloaded along with the above but I do not find your instructions very helpful. (shell suggests you have written the manual for Unix users?) Will I be able to enjoy the benefit of this software in my current Windows environment? Is it necessary for me to run my PC either on Windows NT or Windows 2000 to have the full benefit of your SQL software? Your kind reply, however brief, would be very much appreciated. Regards, Thomas Morton Flat 18 9 Chenies Street London WC1E 7ET Sir, if you are not aware of this, you should know that you are posting to an extremely active mailing list. There are many sirs and madames here, and we try to help each other out. If you give us some clue as to your difficulty, we may be able to help. I've run various versions of MySQL on both win95 and win98. So far, I've had no problems with MySQL, although my present WinTel box, the MS Titanic, has gone down several times with full loss of passengers and crew. (Thank god for backups.) However, the built-in iceberg detector (*CRASH* "Found the iceberg, sir.") is part of Windows, not MySQL. In other words, MySQL should run as well as the operating system. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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: sql date worries
Hi all,
Im kind of strugling with a query for some time now and I need extra input:
What is the desired answer from mysql:
a date.
what is the input:
a week number
What dat do I need:
The date of the monday (week starts at sunday) in the weeknumber.
something like: select date(week(10),1);
But this doesn't work.
Anyone any idea's?
I'm blocked..
Sir, to get a date in the specified week, you can use
Date_add('2001-01-01', INTERVAL number WEEK)
Check on my website for code that finds the date of a specific
weekday, given any date in the week.
http://users.starpower.net/rjhalljr. Click on MySQL on the sidebar,
and select SQL. Look for the date topics.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
-
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 for my application?
Hello all, I am brand new to MySQL. I have very limited experience with SQL type databases. MySQL appears to be a very elaborate product capable of many things. I've glanced at the documentation before writing this. I have 2 very general question to all you 'experienced' MySQL users. Question #1)... My application is in definite need of an SQL database manager. That much I know. But what I'm looking for is a db manager that can handle backward compatibility (ie: database version independence) of my application's data. Putting it in laymen's terms... I have an application that creates and uses a database of data that it uses now (say with version 1.0 of my software). Next year I come out with version 2.0 of my software but I want my software to be capable of running with previous versions of data (stored in the database) as well as new versions. In other words, I want to develop my software to be backward compatible with previous versions of data. I know the application is my responsibility, BUT will MySQL be capable of managing concurrent versions of data inbetween software versions? Sir, I can't figure out what you're asking. The DBMS handles the data, and your application interfaces with the DBMS. If your app changes but the DBMS doesn't, then the way the data is handled doesn't change. The only thing that changes is the way your app interfaces with the DBMS. MySQL is very good about backward compatibility with older versions of itself; if you upgrade to a new version of MySQL, there shouldn't be any need to rewrite your app. Questions #2)... I've used INFORMIX E/SQL in the past but only a little. So I am vaguely familiar with the 'style' of commands one must embed into the code to retrieve data from the database manager. Is there a 'standard' to SQL query command syntax? If I commit to MySQL today and decide to use ORACLE, INFORMIX, GNUSQL, etc. down the road (for whatever reason) will I have to rewrite my db interface handlers? SQL is a standard maintained by a standards organization. No DBMS vendor limits itself to this standard completely. MySQL is no better or worse than any other RDBMS in this regard. You can avoid having to rewrite your db interface by using ODBC or some other database abstraction software. This limits you to the functionality supported by ODBC, and makes your app run a little slower. You can also try to limit your SQL statements to the basic SQL syntax shared by most RDBMSs. This also limits your functionality, but speeds things up a bit. Depending on the developement language you're using, there may be database abstraction software that allows more functionality and runs faster than ODBC. For example, there are a number of abstraction layers available for PHP. - - - Sorry about the simplicity of these questions but you can tell I am exploring what are my best options for introducing a db manager to my application without making costly mistakes from the start. Thank you Attila Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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 Question
What am I doing wrong? The goal is to count the number of entries which match the account preferences. The results are correct, but the times are so far off... mysql select count(*) from STLOUIS left join ClientSTL - on STLOUIS.Dwell=ClientSTL.Dwell where ClientSTL.account='pruitt'; +--+ | count(*) | +--+ | 63 | +--+ 1 row in set (33.28 sec) mysql select count(*) from STLOUIS where Dwell='Con'; +--+ | count(*) | +--+ | 63 | +--+ 1 row in set (0.01 sec) Sir, the left join returns all rows from the left table, which means that it doesn't use any indices on that table. The WHERE condition ensures that rows that don't match on the right table aren't returned, so the left join is unnecessary. Change it to an INNER JOIN, and make sure you have proper indices on both tables. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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: more help with SELECT statement
Hi, A few days ago, I asked the list about a SELECT statement. I received 2 replies that didn't really work but helped me think about the problem some more. In my database I have 4 tables. For this one query, I am matching up data from 3 of these tables and then displaying them on a website. Here is the query: SELECT request.id, request.date, request.type, request.status, faculty.f_name, faculty.l_name, action.id, faculty.id FROM faculty, request LEFT JOIN action ON request.id=action.request_id WHERE request.requested_by=faculty.id; and here is the output created by a php script with data from the above query: http://www.geology.ohio-state.edu/test/rfatest/rfadisplay.php I know it looks ok, but I am not quite sure the SELECT statement is doing what I want it to. I want the query to match up all the requests(request) with the person who requested them(faculty) and then match up all the requests to any actions that our staff might have performed on them (action). However, if no actions were performed yet, I still need those requests displayed. So basically I am matching up one table with two other tables. So if any one can examine the above SELECT statement, and let me know if it does/doesn't look ok, then that would be great. Thanks, Chris Looks good from my porch. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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: substring and a join?
Hello, I have the following query: "SELECT thara_plane.id,thara_plane.nam e,thara_plane.nick,thara_plane.bodytext,thara_category.category " ."FROM thara_plane,thara_category,substring(thara_plane.bodytext,225) as bodytext " ."WHERE ((thara_plane.category = thara_category.id)) " ."ORDER BY thara_plane.time_last DESC"; I need to select the first 255 chars from the field "thara_plane.bodytext". The previous query, however, doesn't work. I have tried it several ways, changine one row and another but I haven't yet found a solution that works. Any ideas? Yours, Lauri Sir, take Substring() out of the FROM clause and put it in the SELECT clause. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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: Recursive queries
Quick searches of Google and the MySQL page didn't turn up anything, so I'll ask here.. Here's what I have: I have a table that among others, have fields called 'section_id' and 'parent_id'. The section_id is the id of the "self" section, and the parent_id is the id of the section that "owns" the self section. (It's sort of like a filesystem where one directory has subdirectories, and so on) Here's what I'm trying to do: I want to start with one section, and go down the tree of sub-sections, picking up information about them on the way. Example: I want to start at the "root" section, and follow down one of it's sub-sections. (Root) | |--- Sports Products || ||--- Golf :: Item1, Item2, Item3, Item4 || ||--- Fishing :: Item5, Item6, Item7, Item8 So it will start at Root, find that Sports Products is attached to Root, get the information I want from Sports Products, then it will find that Golf and Fishing are attached to Sports Products, and get the information from them respectively. (And so on) As it stands, it won't go any further than 2 levels deep from the root section, so I won't need to find a more effecient way to organize the relationships for now. What do you all feel would be the best way to do this? -- Isaac Force [EMAIL PROTECTED] (503)656-2489 http://gorgonous.dhs.org I think that Joe Celko and his publisher should set up some sort of affiliate program so that I earn a little money every time I post the following: 'SQL for Smarties' by Joe Celko, two chapters covering two different approaches to dealing with tree structures. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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: SQL query problem with mysql.
hi, i'm writing a book (wrox publishers) which uses mysql for the sample database. i seem to have encountered a problem with an SQL query. its a simple voting application, with a candidate table, and a vote table: create table candidate ( candidatenumber integer not null auto_increment, firstname varchar(32) not null, lastname varchar(32) not null, politicalparty varchar(32) not null, primary key(candidatenumber)); create table votes ( votenumber integer not null auto_increment, candidatenumber integer, countynumber integer, primary key(votenumber), foreign key(candidatenumber) references candidate, foreign key(countynumber) references county); i want to do a query that shows firstname, lastname, the number of votes for that guy, and the total number of votes cast as illustrated: George, Bush, 2, 10 Al, Gore, 2, 10 Pat, Buchannan, 1, 10 Ralph, Nader, 5, 10 for example ralph nader received 5 votes out of a total of 10 cast. Al gore received 2 votes out of 10 ... you get the idea. here is my query: SELECT Candidate.FIRSTNAME, Candidate.LASTNAME, count(Votes.VoteNumber) , count(select * from Votes) FROM Candidate LEFT OUTER JOIN Votes ON Candidate.CANDIDATENUMBER = Votes.CANDIDATENUMBER GROUP BY Candidate.FIRSTNAME, Candidate.LASTNAME everything works except for the nasty count(select (*) from votes) which seems to work on other db's. if i take it away then it works fine. any ideas on how i could obtain a count of the number of votes cast? thank you for your consideration. Larry Kim [EMAIL PROTECTED] [EMAIL PROTECTED] Sir, MySQL doesn't (yet) support subqueries. Generally, you get around this by using TEMPORARY tables. The following, in slightly different form, ran successfully on the MS Titanic (aka my Wintel box). CREATE TEMPORARY TABLE counts SELECT Candidate.FIRSTNAME, Candidate.LASTNAME, count(*) FROM Candidate LEFT JOIN Votes ON Candidate.CANDIDATENUMBER = Votes.CANDIDATENUMBER GROUP BY Candidate.FIRSTNAME, Candidate.LASTNAME; CREATE TEMPORARY TABLE total_votes SELECT Count(*) AS total_votes FROM votes; SELECT * FROM counts, total_votes; You can also use INSERT INTO counts SELECT "Total", Count(*) FROM votes; SELECT * FROM counts; in place of the last two statements, which will give you the same data in a different format. Your readers may find the second solution easier to understand. The TEMPORARY tables are automatically removed when the connection ends. Note that the FOREIGN KEY clause in your CREATE TABLE statement has no effect in MySQL. It is only there for compatibility with other RDBMSs. In order to maximize speed, MySQL provides no relational integrity constraints other than those applied to primary keys. Foreign keys are not enforced. It is possible that before your book goes to the printer, MySQL will have added subqueries, and one developer or another will have created code that makes it possible to add table types that support integrity constraints. These things are all in the works from various sources. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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
