many queries versus big joins
In general, is it more efficient to do many queries or one "large" query with many joins? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join only the latest entry...
- Original Message -
From: "Ville Mattila" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, December 03, 2003 4:07 AM
Subject: Join only the latest entry...
> Hello there,
>
> I have a table including information about my projects, the structure
> has each id and name. Then I have another table including status
> information of each project: entryid, projectid, status and timestamp.
>
> Is there any possibility to fetch a list of projects with the most
> recent status by one query? I can do it of course by two different
> queries, but I don't find it as very good solution.
Have you tried a straightforward join? Something like
SELECT table1.name FROM table1, table2
WHERE table1.id = table2.entryid AND
{expressing constraining the date in table2 to be fairly
recent}
>
> Thanks,
> Ville
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
- Original Message - From: "Chris Boget" <[EMAIL PROTECTED]> To: "Greg Jones" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, December 02, 2003 8:32 AM Subject: Re: Query Help > > Access. However, when I run it against MySQL I get an error. > > select l.ltsysid,l.lientraknum, c.name from lientrak as l, customer as c > > where l.custsysid=c.custsysid > > and l.ltsysid in (select l2.ltsysid from lientrak as l2 where > > l2.lientraknum > > like '2003-%') > > Sub queries are not going to be available until version 4.1. You'll need > to re-write the above query using an outer (?) join. I'm not sure what the > exact syntax should be and I'm sure someone will pipe up with that info. First impression: it looks like it might be messy if ltsysid isn't unique (i.e., isn't a key). > Chris > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP!
- Original Message - From: "Andy (da man) Rosenblatt" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, November 29, 2003 4:05 PM Subject: HELP! > hi > I bought a book with your mySQL program and seemed to have installed it worng and i cant stop it. I never set a user name or a password. > ~AndyR. 1. Your subject line isn't going to get much attention; it's too vague. 2. You should say which operating system you're using. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql: not found
- Original Message - From: "Kelley Prebil" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, November 17, 2003 9:33 PM Subject: mysql: not found > When I try to start the database with : > > mysql -h host -u user -p > > I get the response : > > mysql: not found > > What does this mean? Installation went smoothly as far as I could tell. Sounds like you don't have your path set up. > Also, when I try to start the database with : > > bin/mysqld_safe --user=mysql & > > It starts the mysqld daemon from the right directory, and then promplty says : > > 031117 18:07:39 mysqld ended > > Any help would be appreciated as I couldn't find anything in the manuals about troubleshooting. > > Kelley > > -- > Kelley Rianna Prebil > http://www.pearlsgirl.com > -- > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The correct way to deal with name_1, name_2, name_3
- Original Message - From: "Paul Fine" <[EMAIL PROTECTED]> To: "'Stephen Fromm'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, November 17, 2003 11:49 AM Subject: RE: The correct way to deal with name_1, name_2, name_3 Thanks. I do mean " Do you mean one column for each name, in which case these would comprise 10 columns, most of which would have NULL most of the time?" SF: OK Is this your suggestion in this case: Table 1 | blahblah | blah | blah | CustID (PK) | Table 2 | CustID (FK) | Name | Table 2 Sample Data | 11 | Bart | | 11 | Jamie| | 11 | Bob | SF: Mostly. Perhaps I'm not getting your design right, but to me CustID should be a PK in Table 2 and an FK in Table 1. It might also be a PK in Table 1, but that depends on the semantics of the table. But maybe that's not what you want because the CustID in the example you gave (in Table 2) is not unique for 3 different rows. Therefore I can select NAME from Table 2 where CustID matches Table 1 and thus have all the names required? SF: That's the basic idea. But given my confusion above, you should provide more details on the meaning of the tables. Thanks! -Original Message- From: Stephen Fromm [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 10:01 AM To: Paul Fine; [EMAIL PROTECTED] Subject: Re: The correct way to deal with name_1, name_2, name_3 - Original Message - From: "Paul Fine" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, November 17, 2003 10:37 AM Subject: The correct way to deal with name_1, name_2, name_3 > Greetings, I would be greatful for any advice on the correct way to do this. > > If I have something dynamic, for example customer names where usually there > are 1 or 2 unique customer names but possibly say up to 10, what is the > "correct" design? > > For example I could simply create a table with name_1, name_2, name_3 > 10. I am sure there is a more efficient way to do this. Could you be more specific? Do you mean one column for each name, in which case these would comprise 10 columns, most of which would have NULL most of the time? Or do you mean a separate row, one of whose columns is customer_name, with one of 10 values? I can't say much because of lack of detail (i.e., what's in the rest of the table), but I'd have TWO tables. In the table you're discussing, I'd have a column called cust_id. In another table, the "customer" table, I'd have two entries, cust_id and cust_name. cust_id in the original table would be a foreign key pointing at the customer table... > Thanks! > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The correct way to deal with name_1, name_2, name_3
- Original Message - From: "Paul Fine" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, November 17, 2003 10:37 AM Subject: The correct way to deal with name_1, name_2, name_3 > Greetings, I would be greatful for any advice on the correct way to do this. > > If I have something dynamic, for example customer names where usually there > are 1 or 2 unique customer names but possibly say up to 10, what is the > "correct" design? > > For example I could simply create a table with name_1, name_2, name_3 > 10. I am sure there is a more efficient way to do this. Could you be more specific? Do you mean one column for each name, in which case these would comprise 10 columns, most of which would have NULL most of the time? Or do you mean a separate row, one of whose columns is customer_name, with one of 10 values? I can't say much because of lack of detail (i.e., what's in the rest of the table), but I'd have TWO tables. In the table you're discussing, I'd have a column called cust_id. In another table, the "customer" table, I'd have two entries, cust_id and cust_name. cust_id in the original table would be a foreign key pointing at the customer table... > Thanks! > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
php and passing implicit connection identifiers
The connection identifier returned by mysql_connect() need not be passed to calls to MySQL-related functions. What is the "scope" of this claim? In particular, if I open a connection and then call a function I wrote myself, which then calls a MySQL related function, can I still omit the connection identifier? TIA, sjfromm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key
- Original Message - From: "Shravan Durvasula" <[EMAIL PROTECTED]> To: "MySQL HELP" <[EMAIL PROTECTED]> Sent: Tuesday, November 11, 2003 12:48 PM Subject: Foreign Key > Hi all: > > I have a table A(Id, Type). Primary Key is "Id" > I have another table B(Id, State). Primary Key is "Id" > > I also have another table C(Id, ConditionId). Primary Key is "Id". But, "ConditionId" could be "Id" values from Table A or Table B. So i want to make it a foreign key. But how can i make the same attribute a foreign key for more than one table? If you really want to follow the canons of relational database theory, you have to be very careful when you do this. You can read up on this stuff in discussions of the EER model (*enhanced* entity-relationship model). I think it's called a "category". The way I've done it is to make another table, with just an Id column; call it AB. Each Id in A appears exactly once in AB, as does each Id in B. (So Id in A is both a pk for A and an fk pointing from A to AB; similarly for B. And of course Id in AB is the pk for AB.) Then ConditionId in C is a fk pointing at AB, not at A or B. > Thanks, > -skd > > > - > Do you Yahoo!? > Protect your identity with Yahoo! Mail AddressGuard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newlines and carriage returns in string data
Is it OK to have actual newline/carriage return characters in string data?
E.g. consider the insert statement
INSERT into table t1 ( ) VALUES('Here is a newline:\nThere it was!');
Now consider the statement, typed as
INSERT into table t1 ( ) VALUES('Here is a newline:
There it was!');
Is the second version legal?
Another way of saying this (I guess) is: what kinds of characters are
allowed in a string constant?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Self Joins and Left Joins ?
- Original Message - From: "Gary Huntress" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, September 17, 2003 12:14 PM Subject: Multiple Self Joins and Left Joins ? > > I'm stumped by this query that I think will involve multiple self joins and > left joins. > > My data looks like: > > +--+--++ > | id | Category | description | > +--+--++ > |1 | color| red| > |2 | color| blue | > |3 | color| yellow | > |4 | size | small | > |5 | size | large | > +--+--++ Are you sure this is a good way to set up your data model? I don't have the time to look everything up, but I would question this design because there's a lot of redundancy going on. That is, while I'm not sure it violates any normal form, clearly there's a "functional dependency" (doubt I'm using that term correctly as per its definition in my RDB text): e.g. if description = red, then category = color. So "category" seems redundant. > I want to permute every combination of description by Category. In the > simple case above I can do > > select color.description , size.description > from mystats as color, mystats as size > where color.Category="color" and size.Category="size"; > +-+-+ > | description | description | > +-+-+ > | red | small | > | blue| small | > | yellow | small | > | red | large | > | blue| large | > | yellow | large | > +-+-+ > > This works fine as long as there are entries present for each category. > The number of categories is not arbitrary (eventually there will be exactly > 14 categories), but there may not be entries in the data table for all > categories. For example, there is a third category "material" but there > are no values... so if I had extended my query above to > > select color.description , size.description, material.description > from mystats as color, mystats as size, mystats as material > where color.Category="color" and size.Category="size" and > material.Category="material"; > > I get no records. > > what I want would be something like this: > > +-+-+-+ > | description | description | description | > +-+-+-+ > | red | small |NULL | > | blue| small |NULL | > | yellow | small |NULL | > | red | large |NULL | > | blue| large |NULL | > | yellow | large |NULL | > +-+-+-+ > > where the third column is null because there are no material categories in > the data. > > I think I need some sort of left join here but in the general case I don't > think it will work because whatever I decide is my "leftmost" Category may > have no entries in the table. > > I know I can do this at the application level with a few seperate queries > and a little more processing, but I'm sure that this can be done with plain > SQL and I'd like to learn how. > > As usual, any help is appreciated. > > Thanks! > > Gary H > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sobig.F on this list
I got a bounced email with an attachment entitled macex.mex.scr, about 72.3 KB, which if IIRC is probably the Sobig.F virus. It appears to have been sent to people on this list (even though the list wasn't in the "to:" line) because the body of the email discussed MySQL. Since Sobig.F forges the "from" line, I don't think the person it was "from" sent it. Rather, IIRC the virus doesn't spoof the IP address that the incoming connection came from, and it appears to be: 217.204.219.154 nslookup says this belongs to mobiletones1-2.dsl.easynet.co.uk That belong to anybody here? If so, and *if* I'm right about the virus (well, worm actually), you might be infected... sjfromm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to code an 'IS - A' relationship ?
Morten, I'd like to help you with actual code, but I can't, because the version of MySQL I use doesn't implement foreign key constraints. In my own code (written in the C API), I plan on checking these constraints myself. But I can't implement them in the tables themselves. Best, Steve Fromm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to code an 'IS - A' relationship ?
> Hello MySQL programmers, > > suppose we have an Enhanced ER diagram, > with entities as classes/ subclasses connected through > some IS-A relationship. > > How can this be Coded in MySQL Please? > > My prerequisites are the basic database texts from > > http://www-db.stanford.edu/~ullman/dscb.html > > http://www.aw-bc.com/info/database/elmasri.html My copy of elmasri has a section "EER-to_Relational Mapping," which I followed when I wanted to do the same thing. It lists 4 methods for modelling EER class/subclass relationships in terms of the relational database model. For me, I did it as follows. Suppose A and B are subclasses of C. Then for C, I had an INT for primary key, plus an ENUM to describe whether the object was in A or B (here, the enum might have two values, 'a' and 'b'). The primary key of A was the same as that of C; similarly for B. The pk of A should satisfy a foreign key constraint with regard to C (similarly for B). If an object cannot belong to both A and B, one has to also check that no pk in C belongs to both A and B. That's not hard to do, but it's not clear to me that it falls under one of the standard integrity issues in the relational model. HTH, sjfromm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: punctuation in fulltext searching
> hmm well sorry to be unclear i know this works but it would return more > results than needed also i cant expect users to add this themselves, like i > would have to add the astrerix to every word in that case like i do to get > all words ;\ I don't know the MySQL issues (I'm now using some FULLTEXT indexes but haven't played with them yet). Note that the issue you're talking about is one of "stemming". The most common examples are singular versus plurals. One solution (if the built-in MySQL stuff proves unsatisfactory) is to build your own stemmer (assuming you're building some kind of interface to MySQL, using the MySQL API). -S > > -Original Message- > From: Egor Egorov [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 19, 2003 7:41 PM > To: [EMAIL PROTECTED] > Subject: Re: punctuation in fulltext searching > > > Daniel Rossi <[EMAIL PROTECTED]> wrote: > > Hi i have just come across an issue where a word is not being searched up > if there is any punctuation ie. AMROZI'S will not be search upon if you type > AMROZI , please help > > Take a look at * operator which you can use in BOOLEAN MODE. > > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.net http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Egor Egorov > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ><___/ www.mysql.com > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Books advice
It depends on what you want to know. I used _Fundamentals of Database Systems_ (Elmasri and Navathe) when I took a DB course. It was pretty good, though my impression is that there might be a "classic" which is better. The problem with the more MySQL-specific books is that you might not learn the more abstract aspects of database design. E.g. some MySQL literature seems to imply that keys and indexes are the same thing, which is not true. Also, a book like the one above will strongly emphasize what an "ideal" RDMS will adhere to, most importantly data integrity, especially referential integrity. Earlier editions of MySQL (including the one I'm using) don't actually enforce foreign key references. And judging from some things I see posted in this list, your DB design will be well-served by learning the fundamentals. I haven't read Celko's books, but my guess is that they're advanced, not foundational, and that you'd be better served by first looking at a foundational book. -S - Original Message - From: "Fawad Siddiqui" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, July 31, 2003 8:05 PM Subject: Books advice Hi, I would like to learn about RDBMS, namely mysql of course, but know really nothing in this area, so have to learn about; RDBMS, SQL and mysql from scratch. In this regard, if anyone knows of any books they think would start me off on the right foot, I would be very grateful. I have done some searching on Amazon, with the following results. 1.Beginning Databases with MySQL by Richard Stones, Neil Matthew 2.MySQL Cookbook by Paul DuBois 3.Managing and Using MySQL by George Reese, et al 4.Inside Relational Databases by Mark Whitehorn, Bill Marklyn 5.Database Design by Ryan K. Stephens, Ronald R. Plew 6.The Practical SQL Handbook: Using SQL Variants by Judith S. Bowman, et al Many thanks in advance for all your help. Fawad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: XML in MySQL
- Original Message - From: "Jon Haugsand" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, June 23, 2003 2:57 AM Subject: Re: XML in MySQL > * [EMAIL PROTECTED] > > geez mysql is an open source product you cannot expect so much too soon over > > a very expensive commercial product which has been out for years , for xml i > > usually generate it on the fly when i am extracting the data using the field > > names as the xml tag nodes. > > Converting a relational dbms into an XML frontend is like trying to > change a Chevrolet into a Beetle. (The problem is of course that too > many managers orders too much xml these days.) Hear, hear! A good place to look if you're interested in the views of relational DB "purists" (and what they think of XML, etc) is http://www.dbdebunk.com/ -S > > -- > Jon Haugsand, [EMAIL PROTECTED] > http://www.norges-bank.no > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inserting illegal values into INT field
It appears that if I try to insert an illegal value into an INT field, the value is set to 0 instead of NULL, even though the field has NULL as a default. Is this documented behavior, and if so, what's the rationale? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
