Re: select on multiple fields in several tables?
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" <[EMAIL PROTECTED]> To: 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 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select on multiple fields in several tables?
Hi, i.e. you want to retrieve all the records of "john smith" from 3 or 4 tables. is it? if so, use table alias. domainnames cannot be used directly.--try with mysql> select * from personal p, payroll r,department d where p.ename="johnsmith" or r.ename="johnsmith" or d.ename="johnsmith"; Thanks ViSolve DB Team. - Original Message - From: "boll" <[EMAIL PROTECTED]> To: "MySQL General Mailing List" Sent: Sunday, February 04, 2007 10:06 PM Subject: select on multiple fields in several tables? 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 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select on multiple fields in several tables?
Ryan Stille wrote: 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 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. Yeah, that would be too complex to manage if the table structure changed. 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). At this point I'm just trying to figure out: Where in the database did the input data get stored? This, then, is how I need to approach it: 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 Thanks, Ryan. -John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 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/[EMAIL PROTECTED]
select on multiple fields in several tables?
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 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
