mysql select multiple tables

This doesn't seem to work. I've tried with, without commas and lots of other things. Just can't seem to find an answer searching the net so am posting.

#!/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 user_id = 8;

EOF

Open in new window


I need to do the following.

bash script one
-Output to a file, including the field names for each table with the records lined up below them.
-Read data inserted into multiple tables by the same user_id.
-Write spacer between each table output, such as 'Table1' so I can see where the tables start/end.
-In one table, read not all of the fields but only some

script two
-A second script which allows me to specify the user_id but to delete all of the data shown in first script

I am looking for examples and solutions even better and can supply what ever missing info there is.

Thanks.
projectsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
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.
0
projectsAuthor Commented:
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.
0
Tomas Helgi JohannssonCommented:
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

Open 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

Open in new window


Regards,
     Tomas Helgi
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

projectsAuthor Commented:
ERROR 1052 (23000) at line 2: Column 'user_id' in where clause is ambiguous
0
projectsAuthor Commented:
I can run the script and enter the password manually each time, that's not a problem.
0
Tomas Helgi JohannssonCommented:
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

Open in new window


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

Regards,
    Tomas Helgi
0
arnoldCommented:
-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?
0
arnoldCommented:
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

Open in new window

0
projectsAuthor Commented:
@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.
0
projectsAuthor Commented:
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
0
Tomas Helgi JohannssonCommented:
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
0
projectsAuthor Commented:
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
0
arnoldCommented:
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

Open 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 ............
0
projectsAuthor Commented:
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_'
0
arnoldCommented:
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?
0
arnoldCommented:
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

Open in new window


see join options from Mysql
http://dev.mysql.com/doc/refman/5.0/en/join.html
0
projectsAuthor Commented:
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.
0
arnoldCommented:
You hould run individual select sequentially

#!/bin/bash
mysql -u root -p -D database << EOF
SELECT * FROM table1 where user_id=244 ;
SELECT * FROM table2 where user_id=244 ;
SELECT * FROM table3 where user_id=244 ;
SELECT * FROM table4 where user_id=244 ;
  
EOF

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
projectsAuthor Commented:
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!
0
arnoldCommented:
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.
0
projectsAuthor Commented:
I don't know perl at all but can get help with bash and php.
0
arnoldCommented:
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.
0
projectsAuthor Commented:
It's all good, works as I need.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.