I am not aware of any SQL dialect that supports a query of the kind you are asking about. If there were such a query it _might_ look something like this
SELECT <column list> FROM <table reference> WHERE ANY_COLUMN(<column name list>) LIKE <search parameter> but like I said, I can't think of any SQL server that has a function like ANY_COLUMN() or its equivalent. Sorry! I think you will need to do a column-by-column comparison (especially if you want to use LIKE or RLIKE). Shawn Green Database Administrator Unimin Corporation - Spruce Pine "mel list_php" <[EMAIL PROTECTED]> wrote on 03/23/2005 11:08:56 AM: > I can find the data, I was just wondering if mysql provides a kind of > generic scan of a whole table. > > I could provide an advanced search, and ask the user what kind of info he > wants to retrieve but I first would like a quickSearch that may retrieve too > much info but is more intuitive. > > As I said, my first idea was to create a script to go through all my tables > and scan the relevant columns (I don't want to scan the id keys for > example), but I was just wondering if given a table it is possible to use > mysql to scan all its columns: > *************************** 1. row *************************** > Table: dbLab > Create Table: CREATE TABLE `dbLab` ( > `labId` tinyint(5) NOT NULL auto_increment, > `name` varchar(50) NOT NULL default '', > `fullName` varchar(250) default NULL, > `adress` varchar(150) NOT NULL default '', > `city` varchar(50) NOT NULL default '', > `country` varchar(50) NOT NULL default '', > `url` varchar(100) NOT NULL default '', > PRIMARY KEY (`labId`) > ) TYPE=MyISAM COMMENT='list of partners' > 1 row in set (0.00 sec) > > If I search for "Smith" I want to retrieve the name Smith, the fullname > Smith, the address or city containing Smith....basically look for the word > in all the columns, a shortener for select * from dbLab where name > like'%Smith%' or fullName like '%Smith%' or adress like '%Smith%'..... > > But maybe this is not a good way to do things? > > Melanie > > > >From: [EMAIL PROTECTED] > >To: "mel list_php" <[EMAIL PROTECTED]> > >CC: [EMAIL PROTECTED],mysql@lists.mysql.com > >Subject: Re: search through one/several tables > >Date: Wed, 23 Mar 2005 09:55:23 -0500 > > > >"mel list_php" <[EMAIL PROTECTED]> wrote on 03/23/2005 06:00:08 AM: > > > > > Unfortunatly they are not, I have something like 30 tables, with I would > >say > > > 10 to 15 fields per table. > > > The number of row per table is quite low, i think it won't exceed > > > 500-1000/table. But I may sometimes have to search into dna sequences > > > (around 5000 atcg characters in any order), so that is quite heavy. > >Maybe > > > for that field a fulltext index would be helpful? > > > > > > Thanks for your help, > > > Melanie > > > > > > > > > >From: [EMAIL PROTECTED] > > > >To: [EMAIL PROTECTED] > > > >CC: mysql@lists.mysql.com > > > >Subject: Re: search through one/several tables > > > >Date: Wed, 23 Mar 2005 10:44:53 +0000 > > > > > > > >"mel list_php" <[EMAIL PROTECTED]> wrote on 23/03/2005 10:14:07: > > > > > > > > > Hi list, > > > > > > > > > > I would like to search for something into one or several tables. > > > > > My first idea was to retrieve the tables' names, then for each of > >them > > > > > retrieve the columns' name and have a look in each of this column. > > > > > Is there a more elegant (fast) way to do that with mysql? > > > > > > > > > > Somebody has some tips/doc where I could look for search engines?My > > > >problem > > > > > is that I don't have one big table with all the data but several > >little > > > >ones > > > > > with few fields, so I don't think solutions like Lucene could work. > > > > > > > >If your tables are all identical, which it sounds like, you want to > >create > > > >a Merge Table: see > > > >http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html > > > > > > > > Alec > > > > > > > >-- > > > >MySQL General Mailing List > > > >For list archives: http://lists.mysql.com/mysql > > > >To unsubscribe: > > > >http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > _________________________________________________________________ > > > Express yourself with cool new emoticons > >http://www.msn.co.uk/specials/myemo > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > >I agree in priciple with Alec. Good database design can and should promote > >good data retreival. However, it sounds from your original question that > >you need to find all records on any table with some bit of information in > >any column. That problem is just too generic to provide specific help > >for. > >If you can't find certain pieces of data, it _may_ be that you haven't > >organized your data to the point that there is a place for everything and > >everything is in its place. There is always (except for the most trivial > >cases) more than one way to organize any set of data, however different > >schemas offer different advantages (size, speed, ease of use, etc). Which > >one will work best for you depends on the nature of your data, your > >database server's limitations (hardware, software, and operating system > >limits), and what you need to get from the data once it's organized. Some > >schemas make it harder to add or update data but make finding it a breeze; > >others are just the opposite. This sounds like a good time to honestly > >review your current schema to make sure it's going to meet your usage > >needs not just your data storage requirements. Ask the people who need to > >use the data you are storing what they need to find and adjust your > >schemas to fit. Sometimes it means asking them to look by using a > >different method as a compromise (you give a little, they give a little). > >Eventually you can work it out. > > > >If you have a schema design issue or if you have a more specific set of > >tables (please post their SHOW CREATE TABLE... results) and a sample query > >that you cannot solve (even if it's only a description of what you need to > >find), I am sure the list will be happy to help. Many of us really enjoy > >those kinds of puzzles. > > > >Shawn Green > >Database Administrator > >Unimin Corporation - Spruce Pine > > > > _________________________________________________________________ > Stay in touch with absent friends - get MSN Messenger > http://www.msn.co.uk/messenger >
