List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:March 23 2005 5:46pm
Subject:Re: search through one/several tables
View as plain text  
Right, my mistake, you would have to use HAVING.

PB

mel list_php wrote:

> 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/
>
>
>


-- 
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

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