Select * from 2 different table

enterume

Hi, is it possible to do a sql statement which select * the field from tableA and tableB. After that, I want the result to be order by 'id' in consequence. How? Any suggestion is appreciate..

Can i use array like $item[$id], then do some sort using array function .. how ?

THanks.

Anon

You want to do a UNION on the two selects of the two tables, and order by the id column of the union\'s result. The union would hence actually be a subquery of an outer query which does the ordering.

enterume

Can you show me a simple sql statement? using UNION with some simle select condition and ordering?

Thanks.

Gerald wrote:

You want to do a UNION on the two selects of the two tables, and order by the id column of the union\'s result. The union would hence actually be a subquery of an outer query which does the ordering.

enterume

I find that MySQL doesnt' support UNION is it?? if so, does it mean i got to create a temporarily table to insert all the data field i need from different table, then drop it after i got what i want???

enterume wrote:

Can you show me a simple sql statement? using UNION with some simle select condition and ordering?

Thanks.

Gerald wrote:

You want to do a UNION on the two selects of the two tables, and order by the id column of the union\'s result. The union would hence actually be a subquery of an outer query which does the ordering.

Anon

You'll find that as you become familiar with relational databses in general that MySQL seems to do less and less of what you want. MySQL is fast, but it does very little.

Consider trying out PostgreSQL or Interbase if you want a database that actually does what relational databases are supposed to do.

The syntax is something to the effect of:

select from (
select from t1 union select * from t2
) order by id;

enterume

when i try it in phpMyAdmin.. it return me the error below:

==============
Error
SQL-query:

select from (select from tblA union select * from tbl😎 order by header_date;

MySQL said: You have an error in your SQL syntax near 'select from tblA union select from tbl😎 order by header_date' line 1

================

DOes MySQL support 'UNION'??

Gerald wrote:

You'll find that as you become familiar with relational databses in general that MySQL seems to do less and less of what you want. MySQL is fast, but it does very little.

Consider trying out PostgreSQL or Interbase if you want a database that actually does what relational databases are supposed to do.

The syntax is something to the effect of:

select from (
select from t1 union select * from t2
) order by id;

Anon

Check out http://www.mysql.com/doc/M/i/Missing_Sub-selects.html

mySQL does not support sub-selects...

I'm actually confused as to what you want - Is table a /b linked e.g.
Table a
id name
1 Mat
2 Pete
Table b
id surname
1 Aug
2 Cres

And you want the results
1 Mat Aug
2 Pete Cres

Or you watn
1 Mat
1 Aug
2 Pete
2 Cres

(does that make sens?)

Anon

Consider trying out PostgreSQL or Interbase if you want a database that actually does what relational databases are supposed to do. <<
Is interbase any good? I've got a RAQ4 with Interbase and mySQL on it. At the momemt I've been moving towards mySQL as it seems to have better support and things like myPHPAdmin (excellent!) - Is there anything like this for Intterbase?

enterume

ok, what i want is .. the db structure for TableA and TableB is almost the samo. I want to do a single sql statement which select all the data i want from both table then display the result in sorting.

Thanks.

Matthew Augier wrote:

Check out http://www.mysql.com/doc/M/i/Missing_Sub-selects.html

mySQL does not support sub-selects...

I'm actually confused as to what you want - Is table a /b linked e.g.
Table a
id name
1 Mat
2 Pete
Table b
id surname
1 Aug
2 Cres

And you want the results
1 Mat Aug
2 Pete Cres

Or you watn
1 Mat
1 Aug
2 Pete
2 Cres

(does that make sens?)

smarlowe

So, how do they tables "relate" to each other. i.e. how do I know which row in table A to match up with a row in table B? Is there some common column that says this row in A is related to this row in B, or what?

If not, then there really is no way to select * from the two and glom them together because there's no "relation" between them.

Anon

Interbase and PostgreSQL are both functionally richer than MySQL.