List:General Discussion« Previous MessageNext Message »
From:mel list_php Date:March 23 2005 5:24pm
Subject:Re: search through one/several tables
View as plain text  
Unfortunatly I can't use the alias txt in the where clause:
Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. 
This is because when the WHERE code is executed, the column value may not 
yet be determined. For example, the following query is illegal:

SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;

http://dev.mysql.com/doc/mysql/en/problems-with-alias.html



>From: Peter Brawley <peter.brawley@stripped>
>Reply-To: peter.brawley@stripped
>To: SGreen@stripped
>CC: mel list_php <list_php@stripped>,  mysql@stripped
>Subject: Re: search through one/several tables
>Date: Wed, 23 Mar 2005 10:46:10 -0600
>
>Shawn, Mel
>
> >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>
>
>Right, but Mel can emulate your ANY_COLUMN with something like
>
>  SELECT CONCAT( col1, ..., colN ) AS txt
>  FROM tbl
>  WHERE txt LIKE '%foobar%';
>
>PB
>
>-----
>
>SGreen@stripped wrote:
>
>>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" <list_php@stripped> 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: SGreen@stripped
>>>>To: "mel list_php" <list_php@stripped>
>>>>CC: Alec.Cawley@stripped,mysql@stripped
>>>>Subject: Re: search through one/several tables
>>>>Date: Wed, 23 Mar 2005 09:55:23 -0500
>>>>
>>>>"mel list_php" <list_php@stripped> 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: Alec.Cawley@stripped
>>>>>>To: list_php@stripped
>>>>>>CC: mysql@stripped
>>>>>>Subject: Re: search through one/several tables
>>>>>>Date: Wed, 23 Mar 2005 10:44:53 +0000
>>>>>>
>>>>>>"mel list_php" <list_php@stripped> 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/mysql?unsub=1
>>>>>>
>>>>>>
>>>>>>
>>>>>_________________________________________________________________
>>>>>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/mysql?unsub=1
>>>>>
>>>>>
>>>>>
>>>>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
>>>
>>>
>>>
>>
>>
>>
>>------------------------------------------------------------------------
>>
>>No virus found in this incoming message.
>>Checked by AVG Anti-Virus.
>>Version: 7.0.308 / Virus Database: 266.8.1 - Release Date: 3/23/2005
>>
>>
>No virus found in this outgoing message.
>Checked by AVG Anti-Virus.
>Version: 7.0.308 / Virus Database: 266.8.1 - Release Date: 3/23/2005
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    
>http://lists.mysql.com/mysql?unsub=1

_________________________________________________________________
Want to block unwanted pop-ups? Download the free MSN Toolbar now!  
http://toolbar.msn.co.uk/

Thread
search through one/several tablesmel list_php23 Mar
  • Re: search through one/several tablesAlec.Cawley23 Mar
    • Re: search through one/several tablesmel list_php23 Mar
      • Re: search through one/several tablesAlec.Cawley23 Mar
      • Re: search through one/several tablesSGreen23 Mar
        • Re: search through one/several tablesmel list_php23 Mar
          • Re: search through one/several tablesSGreen23 Mar
            • Re: search through one/several tablesPeter Brawley23 Mar
              • Re: search through one/several tablesmel list_php23 Mar
                • Re: search through one/several tablesSGreen23 Mar
                • Re: search through one/several tablesPeter Brawley23 Mar
RE: search through one/several tablesPat 006 Adams23 Mar