mysqldump - conditional where clause with two tables ??

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