CODEWITHSUNDEEP
phpmysql
Ei Maung
Ei Maung

Reputation: 7153

Grabbing rows from multiple tables as single result?

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...

Upvotes: 1

Views: 1312

Answers (3)

Frentos
Frentos

Reputation: 169

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 |
+------+---------+

Upvotes: 5

paxdiablo
paxdiablo

Reputation: 881553

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.

Upvotes: 8

Will Hartung
Will Hartung

Reputation: 118661

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

Upvotes: 7

Related Questions