From: Brent Baisley
Date: February 5 2007 2:59pm
Subject: Re: select on multiple fields in several tables?
List-Archive: http://lists.mysql.com/mysql/204798
Message-Id: <01a201c74936$37317c00$1c02a8c0@BrentWD>
MIME-Version: 1.0
Content-Type: text/plain;
	format=flowed;
	charset="iso-8859-1";
	reply-type=response
Content-Transfer-Encoding: 7bit

You can also use the following syntax if you want to pull all the fields from only certain tables:
SELECT table1.*,table2.*,table3.field1,table3.field2 FROM table1, table2, table3 ...

----- Original Message ----- 
From: "Ryan Stille" <ryan@stripped>
To: <mysql@stripped>
Sent: Sunday, February 04, 2007 11:58 AM
Subject: Re: select on multiple fields in several tables?


> boll wrote:
>> Hello-
>>
>> I'm working with an unfamiliar application, trying to figure out where 
>> my data is going.
>>
>> Is it possible to form a query to select from all the columns in 
>> several tables at once? Something equivalent to:
>>    SELECT FROM * WHERE * = 'john smith'; If that's not possible I'd 
>> still like to be able to search each table at once without specifying 
>> the column names:
>>    SELECT FROM <table name>  WHERE * = 'john smith';
>> If that can't be done, can a sub query find the column names to be 
>> selected on?
>>
>> Basically, I'm trying to avoid doing separate selects on each column 
>> in each table.
>>
>> This is using MySQL 5.0.27 .
>> Thanks for any suggestion or alternative methods!
>>
>> -John
>>
> 
> SELECT * FROM table1, table2, table3 WHERE table1.name = 'john smith' 
> AND table2.city = 'Dallas'
> 
> If the tables have fields named the same, this may not work very well.  
> Then you'd need to list out the column names and alias some of them so 
> they come out with different names.
> 
> I don't think there is a way to wildcard the column names like you are 
> asking, I can't imagine a case where you'd need to do that either.  When 
> would every column in your table have the same value? (john smith in 
> your example).
> 
> You can programmatically get a list of columns in a table, then when you 
> are using Perl or PHP or whatever to build your query string, you can 
> loop through the columns to list them.
> 
> -Ryan
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=brenttech@stripped
>