Death of MySQL popularity?
http://www.mysql.com/products/ So the free version is going to include only MyISAM? And you won't be able to connect using MySQL Workbench (and presumably apps like MySQL Query Browser)? Otherwise you have to shell out $2k? Wow. I think it might be time to start seriously looking at Postgres... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Even or Odds numbers
is there a function, using MySQL 5.0v, that can detect if a numerical value is either an Even or Odd number MOD() http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_mod SELECT MOD( X, 2 ) where X is your number (or column name). If 0, it's even if 1 it's odd. thnx, Christoph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: FW: Even or Odds numbers
where X is your number (or column name). If 0, it's even if 1 it's odd. I think you mean, if it is non-zero, then it is odd. If you're MODding using 2 as the second argument, it's always going to be 0 or 1. 2 either divides in to the number evenly, having a remainder of 0, or it'll have a remainder of 1. The former indicates the number is even, the latter odd. thnx, Christoph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: grabbing even addresses?
I was wondering if something was possible, I have an excel file right now of US mailing addresses, and what I need to do is select all the odd numbered addresses on one road, is there an easy way I can do that from MySQL? the addresses could contain 3, 4 or 5 numbers per addresses such as: 123 Main 1232 Main 1233 Main 1234 Main 12345 Main and what I want out of those would be: 1232 Main 1234 Main Any ideas? Thanks for looking! :) Well, if this is something you will be doing a lot, the most efficient way to store the addresses would be to have separate columns for the house number and the street name. Doing that will allow you to run a query as simple as: SELECT * FROM Addresses WHERE (house_number % 2) == 0; If you can't (or don't want to) have separate columns, you can use a regular expression to pull out the house number then operating on it as above.You can read more about mysql and regular expressions here: http://dev.mysql.com/doc/refman/5.0/en/regexp.html thnx, Christoph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
UPDATE jujitsu?
Consider the folowing dataset: +++-+-+---+ | id| Name | Location| OnOffFlag | Description | +++-+-+---+ | 1 | Paper| Cabinet | 0 | Blah| | 2 | Plastic | Cabinet | 0 | Blah| | 3 | China| Cabinet | 1 | Blah| | 4 | Glass| Cabinet | 0 | Blah| | 5 | China| Table | 0 | Blah| | 6 | China| Cabinet | 1 | Blah| +++-+-+---+ Is there a way to, using a single query, set the OnOffFlag to 1 for the record that matches [Name=China AND Location=Table] at the same time setting the OnOffFlag to 0 for records that match [Name=China AND Location!=Table]? I know I can do it in 2 queries but I am curious to know if it can actually be done in 1. thnx, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Conditional Joins
Let's say I have the following tables: Plates table +++-+ | id | Name | Description | +++-+ | 1 | Paper | Blah| | 2 | Plastic| Blah| | 3 | China | Blah| | 4 | Glass | Blah| +++-+ Cups table +++-+ | id | Type | Description | +++-+ | 1 | Paper | Blah| | 2 | Mug| Blah| | 3 | Coffee | Blah| | 4 | Glass | Blah| +++-+ Flatware table +++-+ | id | Form | Description | +++-+ | 1 | Spork | Blah| | 2 | Plastic| Blah| | 3 | Antique| Blah| | 4 | Tin| Blah| +++-+ Inventory table ++++---+ | id | ItemType | ItemId | Owned | ++++---+ | 1 | PLATES | 2 | 17| | 2 | CUPS | 4 | 3 | | 3 | FLATWARE | 3 | 6 | | 4 | CUPS | 3 | 9 | | 5 | CUPS | 1 | 7 | | 6 | FLATWARE | 4 | 12| | 7 | PLATES | 1 | 1 | ++++---+ Is there a way to construct a query so that only the appropriate tables are included as a join? I'm trying to do a conditional (and more elegant) version of the following query: SELECT Inventory.id, CASE Inventory.ItemType WHEN 'PLATES' THEN Plates.Name WHEN 'CUPS' THEN Cups.Type WHEN 'FLATWARE' THEN Flatware.Form END as ItemName Inventory.ItemType, Inventory.ItemId, Inventory.Owned FROM Inventory LEFT OUTER JOIN Plates ON Inventory.ItemType = 'Plates' AND Plates.Id = Inventory.ItemId LEFT OUTER JOIN Cups ON Inventory.ItemType = 'Cups' AND Cups.Id = Inventory.ItemId LEFT OUTER JOIN Flatware ON Inventory.ItemType = 'Flatware' AND Flatware.Id = Inventory.ItemId WHERE Inventory.id IN (2, 4, 5) In the query above, the joins on both the Plates and Flatware table are superfluous because those rows are never selected. I'm not sure I can get out of specifying each possible case in column list part of the query but it seems to me like it should be possible to only join those tables that are relevant based on the conditions set in the WHERE clause. Is something like this even possible? thnx, Christoph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: select records to send to another table in another database
I have a slew of records that went to the wrong database. The tables have the same names and now I want to copy those records over to the correct database. Is there such a mechanism using the cli mysql application in Linux? If the tables have the same schema, you should be able to just do a mysql dump and pipe that back into itself. Something along the lines of: mysqldump old_table | mysql new_table But that will only work if the tables have the same structure. If they don't, you can work with variations of the above. thnx, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select records to send to another table in another database
I have a slew of records that went to the wrong database. The tables have the same names and now I want to copy those records over to the correct database. Is there such a mechanism using the cli mysql application in Linux? For each corresponding table: INSERT INTO db1.mytable SELECT * FROM db2.mytable; And you can do that using the cli mysql app? thnx, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What am I misunderstanding here?
I'm running MySQL version 5.0.33. I have the 2 following queries: SELECT Resource.Id, Resource.Name, Resource.Description, Resource.IsVisible, Resource.UpdatedDate, Resource.CreatedDate FROM Resource INNER JOIN ObjectTarget ON Resource.Id = ObjectTarget.TargetId AND ObjectTarget.TargetType = 'JOE' WHERE ObjectTarget.ObjectType = 'BOB' AND ObjectTarget.ObjectId = 1 AND Resource.IsVisible = 'Y' ORDER BY Resource.Name asc and SELECT OT1.TargetType, OT1.TargetId, Resource.Id, Resource.Name, Resource.Description, Resource.IsVisible, Resource.UpdatedDate, Resource.CreatedDate FROM Resource INNER JOIN ObjectTarget ON Resource.Id = ObjectTarget.TargetId AND ObjectTarget.TargetType = 'JOE' LEFT OUTER JOIN ObjectTarget AS OT1 ON OT1.ObjectType = 'JOE' AND OT1.ObjectId = ObjectTarget.TargetId WHERE ObjectTarget.ObjectType = 'BOB' AND ObjectTarget.ObjectId = 1 AND Resource.IsVisible = 'Y' ORDER BY Resource.Name asc The only difference between them is that the second query includes 2 additional fields (from the OUTER JOINed table) and also the LEFT OUTER JOIN. When run, the first query returns 77 rows of distinct resources. When run, the second query returns 180 rows but within that data set there are only 32 distinct resources. Now, my understanding of OUTER JOINs says that all of the rows from the original query would be returned (77) along with any (if any) related data from the OUTER JOINed table. So if there was no related data in the OUTER JOINed table for the current row, OT1.TargetType and OT1.TargetId would simply be NULL. But if there was related data, there would be an additional row in the resultant data set for each related data row. My expectation is that the second query should have returned the same 77 distinct resources from the first query along with any additional rows correlating to the relating data from the OUTER JOINed table. But that's not what's happening. Where am I going wrong here? thnx, Christoph
Result set flipped on it's axis
Let's say I have the following table: CREATE TABLE `Users` ( `id` blahblah, `firstName` blahblah, `lastName` blahblah, `phone` blahblah, `fax` blahblah, `email` blahblah ); If I do SELECT id, firstName, lastName, email FROM Users, my result set is returned as follows: ++---++-+ | Id | Firstname | LastName | EmailAddress| ++---++-+ | 1 | John | Doe| [EMAIL PROTECTED] | | 2 | Joe | Bob| [EMAIL PROTECTED] | ++---++-+ as expected. But I'm wondering if I could somehow form the query such that the result set is turned on it's axis like so: ++--++ | 1 | Firstname| John | | 1 | LastName | Doe| | 1 | EmailAddress | [EMAIL PROTECTED] | | 2 | Firstname| Joe| | 2 | LastName | Bob| | 2 | EmailAddress | [EMAIL PROTECTED] | ++--++ or some approximation thereof? thnx, Chris
Is this kind of ORDER BY possible?
Let's say that I have the following dataset after an INNER JOIN query: UserName | InventoryItem | InventoryAmount | - | --- Joe | Hammer | 2 Joe | Nails | 7 Joe | Screws | 9 Bob | Hammer | 1 Bob | Hand Saw | 2 Bob | Power Saw | 1 Briggs| Hammer | 4 Briggs| Screwdriver| 1 Briggs| Wrench | 3 Is it possible to order by InventoryAmount but only when InventoryItem has a particular value? Say, Hammer? So that after the sort, the dataset looks like this: UserName | InventoryItem | InventoryAmount | - | --- Bob | Hammer | 1 Bob | Hand Saw | 2 Bob | Power Saw | 1 Joe | Hammer | 2 Joe | Nails | 7 Joe | Screws | 9 Briggs| Hammer | 4 Briggs| Screwdriver| 1 Briggs| Wrench | 3 I know I can do this programatically after the fact while I'm processing the dataset but I'm hoping this can be achieved at the database level. Any information and/or advice would be appreciated! thnx, Christoph
Does this MySQL client exist?
I did a search and couldn't find anything like what I'm looking for and though I doubt something like this does exist, I figured I'd ask anyway. Is there a client (not phpMyAdmin) that can connect to a server (that is running MySQL) using SSH and connect to the database that way? Right now, the only way we are allowed to access the actual server is by using either SSH or SFTP. The only way we can access the MySQL database on that server is either use phpMyAdmin (which I don't particularly care for; not to disparage the hard work of the developers, it's just a matter of personal preference) or use the command line. I'm hoping that there is client software out there that can do what I'm looking for. Does it exist? thnx, Christoph
Re: Does this MySQL client exist?
SSH or SFTP. The only way we can access the MySQL database on that server is either use phpMyAdmin (which I don't particularly care for; not to disparage the hard work of the developers, it's just a matter of personal preference) or use the command line. Use the mysql client, like so: # ssh [EMAIL PROTECTED] [EMAIL PROTECTED] ~ mysql --user=dbuser --password somedatabasename Enter password: XXX mysql SELECT blah blah... Right. That's the command line to which I referred above. thnx, Christoph
Re: Does this MySQL client exist?
There are lots of GUIs for connecting to MySQL databases. MySQL provide some (MySQL Query Browser and MySQL Administrator) but I prefer Toad: http://www.quest.com/toad-for-mysql/ I tried MySQL Administrator but couldn't get it to connect over SSH/SFTP. I'll take a look at toad-for-mysql and NaviCat. thnx, Christoph
Re: Does this MySQL client exist?
Again, you can get absolutely ANY client to connect over an SSH tunnel. You create the tunnel with your SSH client, then use the tunnel to carry your traffic. So you don't need to use an unfamiliar or non-free program to do this. (Any program that offers a connect-over-SSH option is very likely just creating the tunnel for you). How do you go about creating the tunnel? thnx, Christoph
Re: Does this MySQL client exist?
How do you go about creating the tunnel? There are lots of good tutorials online: http://www.google.com/search?q=create+ssh+tunnel Excellent. My thanks to you and to everyone who participated in this thread! thnx, Christoph
