Grabbing rows from multiple tables as single result?

Question

I have 2 tables. Table1 has fields A, B, C, D and Table2 has fields A, B. Field A and B of both tables have same record type. I would like to grab the records from both tables of fields A and B as single result.

Is there any Query or Function in PHP+MySql?

Thanks...

Answer 1

I assume MySql does this:

select a,b from table1 where your_criteria=test_value union select a,b from table2 where your_criteria=test_value

Answer 2

There's a union clause in SQL that does what you want:

select a,b from table1
    where <where-clause>
union all select a,b from table2
    where <where-clause>

or, if you want all fields (spaces for table2):

select a,b,c,d from table1
    where <where-clause>
union all select a,b,' ' as c,' ' as d from table2
    where <where-clause>

The spaces in the second query may need to be expanded to fit the field sizes for c and d.

Answer 3

Union solution confirmed in MySQL Server version: 5.0.51a-3ubuntu5.1 (Ubuntu)

create database foo;
create table bill(a int, b varchar(10));
create table ted(a int, b varchar(10), c datetime, d boolean);
insert into bill values (10, 'foo'), (20, 'bar');
insert into ted values (5, 'splot', now(), true), (10, 'splodge', now(), false);
select a,b from bill where a<=10 union select a,b from ted where a<=10;
+------+---------+
| a    | b       |
+------+---------+
|   10 | foo     |
|    5 | splot   |
|   10 | splodge |
+------+---------+