I want to make a query, retrieve several columns from several tables

I want something in PHP-mysql

I want to make one query, retrieve several or all columns from several tables

Example

query 1: "SELECT * FROM table1"
query 2: "SELECT * FROM table2"
query 2: "SELECT id,name FROM table3"
query 2: "SELECT num,num2 FROM table4"

And so on If you want to add more .

I want to combine all these queries in a single query .

Note: All queries doesn't associated with some, and also I do not want a problem occurs if the table is empty .

an开发者_JAVA技巧y helper here to help me

You can do it with a UNION ALL statement, but you have to specify a coherent (same) structure of output columns for each select. For example:

SELECT (columnA1, columnA2, null, null) FROM tableA
UNION ALL
SELECT (null, null, columnB1, columnB2) FROM tableB

and so on.

You could use UNION, but for that all the queries must have the same number of collumns in the select. You can solve it by add dummy values to select like this: SELECT col1,col2, '' as dummy

EDIT: you cant use * if tables dont have the same number of collumns. What you can do is this:

Suppose your table1 has 3 cols and table2 just 2 cols. You would do the following:

select col1,col2, col3 from table1
UNION
select col1,col2, '' from table2

You need is some way to join the data together for example a column that exists in both tables that is common to each table

eg Accomodation database of sudents

Accomodation_table Accomodation_ID | Accomodation_Name

Student Table Student_ID | Student_Name | Accomodation_ID

Accomodation_ID is the common field

the query would look like this

Select a.Accomodation_Name, b.student_name
from Accomodation_table a, Student_Table b
where a.Accomodation_ID = b.Accomodation_ID

Depends on how you want to combine these tables. Most likely you need to JOIN them:

See: http://www.w3schools.com/sql/sql_join.asp

In case if you want just show them as one table with the same number of columns you can check UNION operator:

See: http://www.w3schools.com/sql/sql_union.asp

If there are no relationships between the tables then you are effectively asking for the following

select *
from table1, table2, table3, table4

which is called the Cartesian join and is normally to be avoided. The number of rows in the result set will be the number of rows in table1 * the number of rows in table2 * the number of rows in table3 * the number of rows in table4!

Normally a Cartesian join occurs when one forgets to add the relationship between tables but you seem to want this deliberately.