independent tables
Everyone has his/her own driving license, and I need to know what kind of person (client or user) is. mysql select userID, clientID from client, user where (clientCodeDrivingLicense= 321321321 || userCodeDrivingLicense = 321321321); ++---+ | userID | clientID | ++---+ | 1 | 2 | | 2 | 2 | | 3 | 2 | | 4 | 2 | | 5 | 2 | ++---+ 5 rows in set (0.00 sec) But, what I want is something like that: ++---+ | userID | clientID | ++---+ | Null | 2 | ++---+ I tried something like this: select COUNT(DISTINCT u.userID), userID, clientID from client, user where (clientCodeDrivingLicense = 321321321 || userCodeDrivingLicense = 321321321); +--++---+ | COUNT(DISTINCT u.userID) | userID | clientID | +--++---+ |5 | 1 | 2 | +--++---+ 1 row in set (0.00 sec) But it wont be efficient enough in the future. I suppose my solution is an Join, but they have no intersection, so, I cant imagine how do it Thank you!! Regards Rocío Gómez Escribano mailto:r.sanc...@ingenia-soluciones.com r.go...@ingenia-soluciones.com Descripción: cid:image002.jpg@01CB8CB6.ADEBA830 Polígono Campollano C/F, nº21T 02007 Albacete (España) Tlf:967-504-513 Fax: 967-504-513 www.ingenia-soluciones.com
Re: Join based upon LIKE
http://www.gedpage.com/soundex.html offers a simple explanation of what it does. One possibility would be building a referential table with only a recordID and soundex column, unique over both; and filling that with the soundex of individual nonjunk words. So, from the titles 1 | Rain in Spain 2 | Spain's Rain you'd get 1 | R500 1 | S150 2 | S150 2 | R500 From thereon, you can see that all the same words have been used - ignoring a lot of spelling errors like Spian. Obviously not a magic solution, but it's a start. - Original Message - From: Jerry Schwartz je...@gii.co.jp To: Johan De Meersman vegiv...@tuxera.be Cc: Jim McNeely j...@newcenturydata.com, mysql mailing list mysql@lists.mysql.com Sent: Monday, 2 May, 2011 4:09:36 PM Subject: RE: Join based upon LIKE [JS] I've thought about using soundex(), but I'm not quite sure how. I didn't pursue it much because there are so many odd terms such as chemical names, but perhaps I should give it a try in my infinite free time. [JS] Thanks for your condolences. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Join based upon LIKE
-Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Tuesday, May 03, 2011 5:31 AM To: Jerry Schwartz Cc: Jim McNeely; mysql mailing list; Johan De Meersman Subject: Re: Join based upon LIKE http://www.gedpage.com/soundex.html offers a simple explanation of what it does. One possibility would be building a referential table with only a recordID and soundex column, unique over both; and filling that with the soundex of individual nonjunk words. So, from the titles 1 | Rain in Spain 2 | Spain's Rain you'd get 1 | R500 1 | S150 2 | S150 2 | R500 From thereon, you can see that all the same words have been used - ignoring a lot of spelling errors like Spian. Obviously not a magic solution, but it's a start. [JS] Thanks. I'm not sure that I could easily build a dictionary of non-junk words, since some of these reports have titles like Toluene Diisocyanate Market Outlook 2008, Toluene Market Outlook 2008, and Toluene: 2009 World Market Outlook And Forecast (Special Crisis Edition). I shall ponder this when I am caught up, or (more likely) in the afterlife. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com - Original Message - From: Jerry Schwartz je...@gii.co.jp To: Johan De Meersman vegiv...@tuxera.be Cc: Jim McNeely j...@newcenturydata.com, mysql mailing list mysql@lists.mysql.com Sent: Monday, 2 May, 2011 4:09:36 PM Subject: RE: Join based upon LIKE [JS] I've thought about using soundex(), but I'm not quite sure how. I didn't pursue it much because there are so many odd terms such as chemical names, but perhaps I should give it a try in my infinite free time. [JS] Thanks for your condolences. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join based upon LIKE
- Original Message - From: Jerry Schwartz je...@gii.co.jp I'm not sure that I could easily build a dictionary of non-junk words, since The traditional way is to build a database of junk words. The list tends to be shorter :-) Think and/or/it/the/with/like/... Percentages of mutual and non-mutual words between two titles should be a reasonable indicator of likeness. You could conceivably even assign value to individual words, so polypropylbutanate is more useful than synergy for comparison purposes. All very theoretical, though, I haven't actually done much of it to this level. My experience in data mangling is limited to mostly should-be-fixed-format data like sports results. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MyISAM key length
Hi, I have a question regarding MyISAM key length in MySQL 5.5.x. Can someone tell me the files (and lines) to modify in order to increase the key length? Thanks. -- Shahryar Ghazi
Re: MyISAM key length
Hi Sha, I think you need to explain yourself better to have a proper answer. Are you talking about MySQL source code? Claudio 2011/5/3 shahryar ghazi shahryar.gh...@gmail.com Hi, I have a question regarding MyISAM key length in MySQL 5.5.x. Can someone tell me the files (and lines) to modify in order to increase the key length? Thanks. -- Shahryar Ghazi -- Claudio
Re: MyISAM key length
Yes, I was talking about modifying MySQL source code. Thanks. On Tue, May 3, 2011 at 10:46 AM, Claudio Nanni claudio.na...@gmail.comwrote: Hi Sha, I think you need to explain yourself better to have a proper answer. Are you talking about MySQL source code? Claudio 2011/5/3 shahryar ghazi shahryar.gh...@gmail.com Hi, I have a question regarding MyISAM key length in MySQL 5.5.x. Can someone tell me the files (and lines) to modify in order to increase the key length? Thanks. -- Shahryar Ghazi -- Claudio -- Shahryar Ghazi IT Professional -- Geeks Worldwide (www.geeksww.com)
Re: Join based upon LIKE
I'm actually enjoying this discussion because I have the same type of issue. However, I have done away with trying to do a full text search in favor of making a table with unique fields where all fields should uniquely identify the group. If I get a dupe, I can clean it up. However, like you, they don't want me to mess with the original data. So, what I have is another table with my good data that my table with my unique data refers to. If a bad record is creased, I don't care I just create my relationship to the table of data I know (read think - I rarely look at this stuff) is good. So, I have 4 fields that should be unique for a group. Two chats and two ints. If three of these match a record in the 'good data' table - there's my relationship. If two or less match, I create a new record in my 'good data' table and log the event. (I haven't gotten to the logging part yet though, easy enough just to look sense none of the fields in 'good data' should match) I'm thinking you might have to dig deeper than me to find 'good data' but I think its there. Maybe isbn, name, publisher + address, price, average pages, name of sales person, who you guys pay for the material, etc etc etc. On May 3, 2011 10:59 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Jerry Schwartz je...@gii.co.jp I'm not sure that I could easily build a dictionary of non-junk words, since The traditional way is to build a database of junk words. The list tends to be shorter :-) Think and/or/it/the/with/like/... Percentages of mutual and non-mutual words between two titles should be a reasonable indicator of likeness. You could conceivably even assign value to individual words, so polypropylbutanate is more useful than synergy for comparison purposes. All very theoretical, though, I haven't actually done much of it to this level. My experience in data mangling is limited to mostly should-be-fixed-format data like sports results. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ag4ve...@gmail.com
RE: Join based upon LIKE
My situation is sounds rather simple. All I am doing is matching a spreadsheet of products against our database. My job is to find any matches against existing products and determine which ones are new, which ones are replacements for older products, and which ones just need to have the publication date (and page count, price, whatever) refreshed. Publisher is no problem. What I have for each feed is a title and (most of the time) an ISBN or other identification assigned by the publisher. Matching by product ID is easy (assuming there aren't any mistakes in the current or previous feeds); but the publisher might or might not change the product ID when they update a report. That's why I also run a match by title, and that's where all the trouble comes from. The publisher might or might not include a mix of old and new products in a feed. The publisher might change the title of an existing product, either on purpose or by accident; they might simply be sloppy about their spelling; or (and this is where it is critical) the title might include a reference to some time period such as a year or a quarter. I think we'd better pull the plug on this discussion. It doesn't seem like there's a ready solution. Fortunately our database is small, and most feeds are only a few hundred products. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: shawn wilson [mailto:ag4ve...@gmail.com] Sent: Tuesday, May 03, 2011 4:08 PM Cc: mysql mailing list Subject: Re: Join based upon LIKE I'm actually enjoying this discussion because I have the same type of issue. However, I have done away with trying to do a full text search in favor of making a table with unique fields where all fields should uniquely identify the group. If I get a dupe, I can clean it up. However, like you, they don't want me to mess with the original data. So, what I have is another table with my good data that my table with my unique data refers to. If a bad record is creased, I don't care I just create my relationship to the table of data I know (read think - I rarely look at this stuff) is good. So, I have 4 fields that should be unique for a group. Two chats and two ints. If three of these match a record in the 'good data' table - there's my relationship. If two or less match, I create a new record in my 'good data' table and log the event. (I haven't gotten to the logging part yet though, easy enough just to look sense none of the fields in 'good data' should match) I'm thinking you might have to dig deeper than me to find 'good data' but I think its there. Maybe isbn, name, publisher + address, price, average pages, name of sales person, who you guys pay for the material, etc etc etc. On May 3, 2011 10:59 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Jerry Schwartz je...@gii.co.jp I'm not sure that I could easily build a dictionary of non-junk words, since The traditional way is to build a database of junk words. The list tends to be shorter :-) Think and/or/it/the/with/like/... Percentages of mutual and non-mutual words between two titles should be a reasonable indicator of likeness. You could conceivably even assign value to individual words, so polypropylbutanate is more useful than synergy for comparison purposes. All very theoretical, though, I haven't actually done much of it to this level. My experience in data mangling is limited to mostly should-be-fixed-format data like sports results. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ag4ve...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join based upon LIKE
Dear Jerry, I've been silently following this discussion because I've missed the original question. But from your last explanation, now it really looks you have a data quality kind of issue, which is by far related with MySQL. Indeed, in Data Quality, there is *never* a ready solution, because the source is tipically chaotic May I suggest you to explore Google Refine? It seems to be able to address all those issues quite nicely, and the clustering might solve your problem at once. You shall know, however, how to export the tables (or a usable JOIN) as a CSV, see SELECT ... INTO OUTFILE for that. Hope it helps, -NT Em 03-05-2011 21:34, Jerry Schwartz escreveu: My situation is sounds rather simple. All I am doing is matching a spreadsheet of products against our database. My job is to find any matches against existing products and determine which ones are new, which ones are replacements for older products, and which ones just need to have the publication date (and page count, price, whatever) refreshed. Publisher is no problem. What I have for each feed is a title and (most of the time) an ISBN or other identification assigned by the publisher. Matching by product ID is easy (assuming there aren't any mistakes in the current or previous feeds); but the publisher might or might not change the product ID when they update a report. That's why I also run a match by title, and that's where all the trouble comes from. The publisher might or might not include a mix of old and new products in a feed. The publisher might change the title of an existing product, either on purpose or by accident; they might simply be sloppy about their spelling; or (and this is where it is critical) the title might include a reference to some time period such as a year or a quarter. I think we'd better pull the plug on this discussion. It doesn't seem like there's a ready solution. Fortunately our database is small, and most feeds are only a few hundred products. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: shawn wilson [mailto:ag4ve...@gmail.com] Sent: Tuesday, May 03, 2011 4:08 PM Cc: mysql mailing list Subject: Re: Join based upon LIKE I'm actually enjoying this discussion because I have the same type of issue. However, I have done away with trying to do a full text search in favor of making a table with unique fields where all fields should uniquely identify the group. If I get a dupe, I can clean it up. However, like you, they don't want me to mess with the original data. So, what I have is another table with my good data that my table with my unique data refers to. If a bad record is creased, I don't care I just create my relationship to the table of data I know (read think - I rarely look at this stuff) is good. So, I have 4 fields that should be unique for a group. Two chats and two ints. If three of these match a record in the 'good data' table - there's my relationship. If two or less match, I create a new record in my 'good data' table and log the event. (I haven't gotten to the logging part yet though, easy enough just to look sense none of the fields in 'good data' should match) I'm thinking you might have to dig deeper than me to find 'good data' but I think its there. Maybe isbn, name, publisher + address, price, average pages, name of sales person, who you guys pay for the material, etc etc etc. On May 3, 2011 10:59 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Jerry Schwartz je...@gii.co.jp I'm not sure that I could easily build a dictionary of non-junk words, since The traditional way is to build a database of junk words. The list tends to be shorter :-) Think and/or/it/the/with/like/... Percentages of mutual and non-mutual words between two titles should be a reasonable indicator of likeness. You could conceivably even assign value to individual words, so polypropylbutanate is more useful than synergy for comparison purposes. All very theoretical, though, I haven't actually done much of it to this level. My experience in data mangling is limited to mostly should-be-fixed-format data like sports results. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ag4ve...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How do I get more pages in mySQL Workbench?? WAS: Any table visualization tools with wires connecting the actual columns?
I just discovered that MySQL Workbench actually WILL connect columns. Use the #6 wire type shortcut (bottom one). However, I must be retarded or blind because I can't figure out how to make the diagram have more than the default of 2 pages. I have a database with 50 tables and they just don't fit. In fact I only noticed this after printing out the page that many were missing. Turns out you can't scroll down to see them, you can only zoom way out and see that tables are off the white page. :-\ Oddly though, I imported another database with 88 tables and it automatically made 4 pages for me. Anyone know how to get more pages added? From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Friday, April 01, 2011 4:27 PM To: mysql@lists.mysql.com Subject: Any table visualization tools with wires connecting the actual columns? I am evaluating various tools for diagram generating of existing databases on some smaller databases (9 tables or so) first. The two I've tried so far are these: http://dev.mysql.com/downloads/workbench/ http://www.sqlmaestro.com/download/#mysql Both _seem_ robust and cosmetically polished, but feel to me lacking the most obvious and key component of the whole purpose to make an EER diagram. I don't understand in workbench, why it creates new keys for me on existing tables. Maestro doesn't do this nonsense. It isn't the tools business where I have keys, it only needs to be concerned with what links to what -- that I tell it to. It's further exacerbated by the fact that the documentation indicates these aren't even REAL keys, they are cosmetic only! WTF? Why add confusion guys? 1. Neither one seem to be smart enough to automatically know that columns of the same name should be linked, and furthermore they should be linked from all tables to the one where that column name is the PK. my tables don't have true InnoDB FKs setup. And some tables are MYISAM (as they're significantly faster). But I do use keys and I do have sane naming conventions, so I don't understand why they can't use the names, and if there are multiple tables (for some unlikely reason) then just prompt me which table to use. Which leads me to the second and third problems... So I manually have started to draw the connections, but: 2. How can I make the wires stick to a column on the left or right edge, so that I can have a direct visual link between the columns. Right now, it seems they float around the edge of the table box. That's sort of useless isn't it? it's like saying, well, something in this table points to something in that table.?! I would think that two programs with such high version numbers would have this feature. Maybe I'm missing a configuration or some way I'm supposed to do it? 3. Some of my databases point to tables in other databases on the same server. It would be useful if I could make a wire that indicates this. Are there other (better) options out there for this? I really don't want to do this in Visio or make a printout of the table boxes and tape string to my walls to visualize all the databases, tables and columns. -Daevid.
