This is a discussion on mysqldump - conditional where clause with two tables ?? within the MySQL Database forums, part of the Database Forums category; I am trying to get a conditional mysqldump from two related, based on a specified relationship between the table records, ...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
|
|||
|
I am trying to get a conditional mysqldump from two related, based on
a specified relationship between the table records, and I am having trouble constructing the statement. Here is the situation. Two tables are involved, configured as follows: company ( company_id, ... ) company_site ( company_id, site_id ) I want to get a dump of both company records and corresponding company_site records for a specified site_id. I tried the following mysqldump command: (Values in <...> are placeholders, not the actual values used) mysqldump --user="<username>" --password="<password>" --where="company.company_id=company_site.company_id and company_site.site_id=<site_id>" wsadev_main company company_site > text_company_dump.txt; I get the following error message: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `company` WHERE company.company_id=company_site.company_id and company_site.site_id=<site_id>': Unknown column 'company_site.company_id' in 'where clause' (1054) Does mysqldump have the capacity to conduct selective dumps of table records on more than one table; and, if so, what is the proper construction of the command? Tim |
|
|||
|
On Apr 28, 9:27*am, Tim <t...@nowhere.com> wrote:
> I am trying to get a conditional mysqldump from two related, based on > a specified relationship between the *table records, ... > Does mysqldump have the capacity to conduct selective dumps of table > records on more than one table; and, if so, what is the proper > construction of the command? > > Tim I don't think mysqldump is capable of any kind of join. So you are going to be limited to SELECT and SELECT INTO OUTFILE. |
|
|||
|
On Tue, 28 Apr 2009 23:42:55 -0700 (PDT), toby
<toby@telegraphics.com.au> wrote: >On Apr 28, 9:27*am, Tim <t...@nowhere.com> wrote: >> I am trying to get a conditional mysqldump from two related, based on >> a specified relationship between the *table records, ... >> Does mysqldump have the capacity to conduct selective dumps of table >> records on more than one table; and, if so, what is the proper >> construction of the command? >> >> Tim > >I don't think mysqldump is capable of any kind of join. So you are >going to be limited to SELECT and SELECT INTO OUTFILE. Thanks. That is quite helpful. At least I can now solve the problem. Tim |
|
|||
|
Tim wrote:
> I am trying to get a conditional mysqldump from two related, based on > a specified relationship between the table records, and I am having > trouble constructing the statement. Here is the situation. > > Two tables are involved, configured as follows: > company ( > company_id, > ... > ) > > company_site ( > company_id, > site_id > ) > > I want to get a dump of both company records and corresponding > company_site records for a specified site_id. I tried the following > mysqldump command: > > (Values in <...> are placeholders, not the actual values used) > > mysqldump --user="<username>" --password="<password>" > --where="company.company_id=company_site.company_id and > company_site.site_id=<site_id>" wsadev_main company company_site > > text_company_dump.txt; > > I get the following error message: > Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `company` > WHERE company.company_id=company_site.company_id and > company_site.site_id=<site_id>': Unknown column > 'company_site.company_id' in 'where clause' (1054) > > Does mysqldump have the capacity to conduct selective dumps of table > records on more than one table; and, if so, what is the proper > construction of the command? > > Tim Well, it sort-of does... as long as the data gets imported into a single table... create a view using your query and use mysqldump to export that data using --tables |
|
|||
|
On Apr 29, 9:49*am, Michael Austin <maus...@firstdbasource.com> wrote:
> Tim wrote: > > I am trying to get a conditional mysqldump from two related, based on > > a specified relationship between the *table records, and I am having > > trouble constructing the statement. *Here is the situation. > > > Two tables are involved, configured as follows: > > company ( > > * *company_id, > > * *... > > * *) > > > company_site ( > > * *company_id, > > * *site_id > > * * * * * *) > > > I want to get a dump of both company records and corresponding > > company_site records for a specified site_id. *I tried *the following > > mysqldump command: > > > (Values in <...> are placeholders, not the actual values used) > > > *mysqldump --user="<username>" --password="<password>" > > --where="company.company_id=company_site.company_id and > > company_site.site_id=<site_id>" wsadev_main company company_site > > > text_company_dump.txt; > > > I get the following error message: > > Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `company` > > WHERE company.company_id=company_site.company_id and > > company_site.site_id=<site_id>': Unknown column > > 'company_site.company_id' in 'where clause' (1054) > > > Does mysqldump have the capacity to conduct selective dumps of table > > records on more than one table; and, if so, what is the proper > > construction of the command? > > > Tim > > Well, it sort-of does... as long as the data gets imported into a single > table... > > create a view using your query and use mysqldump to export that data > using --tables Brilliant. |
|
|||
|
toby wrote:
> On Apr 29, 9:49 am, Michael Austin <maus...@firstdbasource.com> wrote: >> Tim wrote: >>> I am trying to get a conditional mysqldump from two related, based on >>> a specified relationship between the table records, and I am having >>> trouble constructing the statement. Here is the situation. >>> Two tables are involved, configured as follows: >>> company ( >>> company_id, >>> ... >>> ) >>> company_site ( >>> company_id, >>> site_id >>> ) >>> I want to get a dump of both company records and corresponding >>> company_site records for a specified site_id. I tried the following >>> mysqldump command: >>> (Values in <...> are placeholders, not the actual values used) >>> mysqldump --user="<username>" --password="<password>" >>> --where="company.company_id=company_site.company_id and >>> company_site.site_id=<site_id>" wsadev_main company company_site > >>> text_company_dump.txt; >>> I get the following error message: >>> Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `company` >>> WHERE company.company_id=company_site.company_id and >>> company_site.site_id=<site_id>': Unknown column >>> 'company_site.company_id' in 'where clause' (1054) >>> Does mysqldump have the capacity to conduct selective dumps of table >>> records on more than one table; and, if so, what is the proper >>> construction of the command? >>> Tim >> Well, it sort-of does... as long as the data gets imported into a single >> table... >> >> create a view using your query and use mysqldump to export that data >> using --tables > > Brilliant. yeah, I know :) |