mysql select multiple tables

Please poin the show Crete table
Output for each of the tables.

This is to see how the tables related.
Does each table have a user_id column?

The use of -p means the script will prompt for a password.

Yes, each table has a user_id column.
Not sure what you mean by 'show create table'
The only relation between the tables is that the same user_id would have sent data to each one.

Hi!

First of all you need to provide the users password and to output to a file you do it like this

#!/bin/bash
# user_id should be entered at command line as in $1
myfile=/tmp/queryoutfile.txt
mysql -u root -p=somepass -D database << EOF

SELECT * FROM table1 WHERE user_id = 8
INTO OUTFILE '$myfile';
EOF

Select allOpen in new window

To have the user and password passed to your script it would look like this

#!/bin/bash
# user_id  should be entered at command line as in $1 and passw in $2
myfile=/tmp/queryoutfile.txt
mysql -u=$1 -p=$2 -D database << EOF

SELECT * FROM table1 WHERE user_id = 8
INTO OUTFILE '$myfile';
EOF

Select allOpen in new window

Regards,
     Tomas Helgi

ERROR 1052 (23000) at line 2: Column 'user_id' in where clause is ambiguous

I can run the script and enter the password manually each time, that's not a problem.

Hi!


Try this

#!/bin/bash
# user_id  should be entered at command line as in $1 and passw in $2
myfile=/tmp/queryoutfile.txt
mysql -u=$1 -p=$2 -D database << EOF

SELECT * FROM table1
INTO OUTFILE '$myfile';
EOF

Select allOpen in new window

Your user_id error says that that column doesn't exist in table1.

Regards,
    Tomas Helgi

-ppassword or --password=password to include the password in the script.

Show create table deals with provide info on the construct/definition of each table.  The issue might be related to your select including all columns

Your error is that mysql is not clear on which tables' user_id your clause applies to, table1.user_id, table2.user_id or table3.user_id.

Do these tables have a different set of information such that you want all the columns from all the tables as one huge table?

Try this:

#!/bin/bash
# user_id should be entered at command line as in $1

mysql -u root -p -D database << EOF


SELECT * FROM table1, table2, table3, table4 WHERE table1.user_id = 8 and table2.user_id=8 and table3.user_id=8 and table4.user_id=8;

EOF

Select allOpen in new window

@TomasHelgi; I need to specify the user_id and the multiple tables.

@Arnold; The top says user id is to be entered at command line yet user_id = 8 below.
Either way, that leads to nothing.

I think this is getting all confused :).

This following code works fine but it only reads one table.

#!/bin/bash
mysql -u root -p -D database << EOF

SELECT * FROM table1 WHERE user_id = 163;
EOF

Hi!

To read from multiple tables you will need to join them together in the sql.
IF they have the same columns you could do it like this.

SELECT * FROM table1 WHERE user_id = 163
union all
SELECT * FROM table2 WHERE user_id = 163;

and so on for all identical tables ending it with an ;

Regards,
    Tomas Helgi

Never works, only works with one SELECT line as I posted above.

#!/bin/bash
mysql -u root -p -D database << EOF
SELECT * FROM table1 WHERE user_id = 244;
union all
SELECT * FROM table2 WHERE user_id = 244;

EOF


When I run it, I get the following error.

ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union all
SELECT * FROM table2 WHERE user_id = 244' at line 1

try the following,

#!/bin/bash
mysql -u root -p -D database << EOF
SELECT * FROM table1 a outer join table2 b on b.user_id=a.user_id outer join table3 c on c.user_id=b.user_id outer join table4 d on d.user_id=c.user_id
where a.user_id=244 
  
EOF

Select allOpen in new window

there is a need to see what the tables are and how they relate.  Are these tables an assortment of different data?
writing query blind without information on what is in the table is ............

I just want to read all of the rows which this user_id owns in the multiple tables. In fact, I only need table1, table2 and table3.

The above gave the following error.

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join table2 b on b.user_id=a.user_id outer join table3 c on c.user_' at line 1

All user_id entries are complete. The above error seems to imply that one entry is only 'c.user_'

I understand that you want to extract data from multiple tables, but in order to properly write a query, one has to know what and how the tables interact.

Can you please post the show create table <table1>
This will output the create table <table1> syntax so it is possible to know what

What do you get when you run your original query without the where clause limitation?

Or try this

#!/bin/bash
mysql -u root -p -D database << EOF
SELECT * FROM table1 a left join (table2, table3, table4) on (table2.user_id=a.user_id and Table3.user_id=a.user_id and table4.user_id=a.user_id)
where a.user_id=244 
  
EOF

Select allOpen in new window

see join options from Mysql
http://dev.mysql.com/doc/refman/5.0/en/join.html

That worked but isn't what I need :).
All of the db labels/fields were stuffed together at the top and all of the data was mashed together in the display.

What I need is to display each table, one after the other, hopefully with a break between each showing it's labels/fields.

Yes, that works perfectly and will save me TONS of time instead of having to look at each table one at a time. Accepting solution.

I changed the user_id to $1 for each table so I can call it all from command line now.
Sure would love to send the output to a text file because there is too much on the console screen


The next things I would love to do would be

-Line up all the fields with the data for each table, making it easier to see the output
-Maybe even add the table name in between each with a space to make things easier to see
-Add the ability to specify which tables I'd like to see, could be 1,2,3,4 for example instead of all

-A second script which would do exactly the same but this time, delete all of the same data so I don't have to do it per table

Thanks again!

Are you familiar with perl? Using it to connect/query might get you closer to the formatting you want.

There are also ways to define the output I.e. Use commas, versus | as field separarator.


.....

You could use
\t outputfile.sql as the first line after the mysql line. \t opens the file referenced for output.

Or you can have the > redirect if the bash script to a file.

I don't know perl at all but can get help with bash and php.

Php is somewhat similar
What are looking for to convert the output using the | as separators to commas?

Passing the output through sed/awk might help in reformatting the output.

It's all good, works as I need.