From: Elton Hughes
Date: April 22 1999 4:24pm
Subject: Re: searching two tables
List-Archive: http://lists.mysql.com/mysql/2256
Message-Id: <1287320341-61866514@novapic.org>
MIME-Version: 1.0
Content-Type: text/plain; charset="US-ASCII"

Daniel wrote:

>A Basic question:
>I need to search though two tables for a match.
>I don't need to search for a match between the two tables.
>
>something like
>select bar from table_1, table_2 where a='foo';
>
>I know I need to make an alias table to combine the two tables into one,
>and then select from the combined table, but I do not know how.

Hi Daniel,

I am new to MySQL, but am experienced with Informix and SQL. I am 
assuming that you want to know the value of 'bar' in either table if 
either table_1.a or table_2.a has a value of 'foo'. 

In short, you cannot do this as a single statement. In your select 
statement, bar will need to be defined/declared to be a part of one table 
or another. Otherwise you will receive an 'ambiguous field' error 
message. SQL will want you to be a little more specific about what field 
you want to be selected. You can do this by changing your select to be 
table_1.bar, table2.bar. But this still does not solve the problem, 
because in the 'where' statement you must be specific once again. Which 
field 'a', from table_1 or table_2? 

I would use two separate statements to solve this problem, assuming I 
understand your problem.

I hope this helps.

Elton


======================================================================
Elton Hughes                               505 W. Olive Ave. Suite 550
NOVA Private Industry Council                      Sunnyvale, CA 94086
http://www.novapic.org/                            Phone: 408-730-7232
----------------------------------------------------------------------