Mysql selecting from two tables

Question

Can anybody tell me how to select data from two tables, without having to use join?

Something like this:

SELECT t1.*, 
       t2.*
  FROM table1 t1, 
       table2 t2

###Clarification I have these two tables, that have the same fields. IE: table1 contains data from 2011 and table2 contains data in 2012. I want to get them all.

###Further clarification: The result set desired can be produced by:

(SELECT tr.full_name, tr.headlines, tr.content, tr.stamp, tr.person_key
 FROM tbl_transactions tr
          JOIN persons p ON p.person_key = tr.person_key
          JOIN teams t ON (pp.membership_id = t.id and pp.membership_type = 'teams')
 WHERE t.team_key = '')
UNION
(SELECT tr.full_name, tr.headlines, tr.content, tr.stamp, tr.person_key
 FROM tbl_transactions_bk_2012 tr
          JOIN persons p ON p.person_key = tr.person_key
          JOIN teams t ON (pp.membership_id = t.id and pp.membership_type = 'teams')
 WHERE t.team_key = ''

and the OP wishes to see if there are alternative ways to speed this up ("I tried to use UNION in between those queries. but query speed took 0.1887 secs. it's kinda slow.")

(@Jetoox: if this is not your intent, please edit your question and clarify).

i have these two tables that has the same fields, example table1 contains data from 2011 and table2 contains data in 2012. I want to get them all. — Jetoox, Feb 27 '12 at 05:33
possible duplicate of [MySQL - Selecting data from multiple tables all with same structure but different data](http://stackoverflow.com/questions/409705/mysql-selecting-data-from-multiple-tables-all-with-same-structure-but-differen) — Ignacio Vazquez-Abrams, Feb 27 '12 at 05:40
both of these tables contains the same example person_key, and when i run the query. mysql say's unknown column person_key. — Jetoox, Feb 27 '12 at 05:41
Are you *sure* each table contains a column named `person_key`? — mathematical.coffee, Feb 27 '12 at 05:42
@DawnUser: You cut out pertinent information in the question, possibly due to editing an older copy of it. Mind what you are doing. — OMG Ponies, Feb 27 '12 at 05:43
@OMGPonies there are tags to specify it is MySQL related... "without having to use join" is crucial from my point of view and now, you have cut that out. — Emile Bergeron, Feb 27 '12 at 05:45
@Jetoox: Can you post the relevant output of `show columns from table1;` and table2 to verify you have a column with that name? And the exact query you are running (edit your question with them)? "Unknown column xxx" usually means that that column doesn't exist in that table. — mathematical.coffee, Feb 27 '12 at 05:46
@DawnUser: I explained why I responded as I did to your suggested edit. The tag/title detail is trivial compared to the question. — OMG Ponies, Feb 27 '12 at 05:47
i created two queries for that and use php array_merge. so that's why i want to make it just one query. I tried to use UNION in between those queries. but query speed took 0.1887 secs. it's kinda slow. — Jetoox, Feb 27 '12 at 05:48

mathematical.coffee · Accepted Answer · 2012-02-27T06:02:07.413

29

Just put the join condition in the WHERE clause:

SELECT t1.*, t2.*
FROM table1 t1, table2 t2
WHERE t1.id = t2.t1_id

That is an inner join, though.

UPDATE

Upon looking at your queries: In this particular case, there is no relation between tbl_transactions and tbl_transactions_bk_2012 (i.e. joining these on person_key is meaningless because there is no relationship between the two tables in the way that (say) tbl_transactions and persons are related).

Then, you should use the UNION approach. Trying to join the first query to the second using either JOIN or FROM xx, yy WHERE xx.id=yy.id is meaningless and won't give you the results you need.

By the way, in the future, put your current query/attempt in your post - as you can see it will prevent you from getting answers that aren't appropriate for your question (as my first attempt was).

edited Feb 27 '12 at 06:02

answered Feb 27 '12 at 05:33

mathematical.coffee

55,977
11
154
194

both of these tables contains the same example person_key, and when i run the query. mysql say's unknown column person_key. – Jetoox Feb 27 '12 at 05:40
Please post your *exact* query that you are using, and also the output of `show columns from table1 where Field='person_key'` and `show columns from table2 where Field='person_key'`. If your two tables are related by a *common* key `person_key`, use the `WHERE` condition. If your two tables are entirely unrelated, use the `UNION`. – mathematical.coffee Feb 27 '12 at 05:53
(SELECT tr.full_name,tr.headlines,tr.content,tr.stamp,tr.person_key FROM tbl_transactions tr JOIN persons p ON p.person_key = tr.person_key JOIN teams t ON (pp.membership_id = t.id and pp.membership_type = 'teams') WHERE t.team_key = '') UNION (SELECT tr.full_name,tr.headlines,tr.content,tr.stamp,tr.person_key FROM tbl_transactions_bk_2012 tr JOIN persons p ON p.person_key = tr.person_key JOIN teams t ON (pp.membership_id = t.id and pp.membership_type = 'teams') WHERE t.team_key = '') – Jetoox Feb 27 '12 at 05:55
@Jetoox: in this particular case, since there is no relation between `tbl_transactions` and `tbl_transactions_bk_2012` (i.e. joining these on `person_key` is meaningless because there is no relationship between the two tables in the way that (say) `tbl_transactions` and `persons` are related), you should use the `UNION` approach - trying to join the first query to the second using either `JOIN` or `FROM xx, yy WHERE xx.id=yy.id` is meaningless and won't give you the results you need. – mathematical.coffee Feb 27 '12 at 06:00

score 12 · Answer 2 · edited Jan 22 '21 at 14:54

12

You want UNION.

(SELECT tr.full_name, tr.headlines, tr.content, tr.stamp, tr.person_key
 FROM tbl_transactions tr
          JOIN persons p ON p.person_key = tr.person_key
          JOIN teams t ON (pp.membership_id = t.id and pp.membership_type = 'teams')
 WHERE t.team_key = '')
UNION
(SELECT tr.full_name, tr.headlines, tr.content, tr.stamp, tr.person_key
 FROM tbl_transactions_bk_2012 tr
          JOIN persons p ON p.person_key = tr.person_key
          JOIN teams t ON (pp.membership_id = t.id and pp.membership_type = 'teams')
 WHERE t.team_key = ''

edited Jan 22 '21 at 14:54

Ryabchenko Alexander

10,057
7
56
88

answered Feb 27 '12 at 05:34

Ignacio Vazquez-Abrams

776,304
153
1,341
1,358

Answer from another question about Union: http://stackoverflow.com/a/409720/1218980 – Emile Bergeron Feb 27 '12 at 05:38
both of these tables contains the same example person_key, and when i run the query. mysql say's unknown column person_key. – Jetoox Feb 27 '12 at 05:41

score 4 · Answer 3 · answered Feb 27 '12 at 05:34

4

select t1.*, t2.* from table1 t1, table2 t2
where t1.fkey = t2.pkey

answered Feb 27 '12 at 05:34

silly

7,789
2
24
37

both of these tables contains the same example person_key, and when i run the query. mysql say's unknown column person_key. – Jetoox Feb 27 '12 at 05:40

score 2 · Answer 4 · edited Feb 27 '12 at 09:03

2

select t1.* , t2.*
from t1, t2 where t1.id=t2.id;

edited Feb 27 '12 at 09:03

animuson

53,861
28
137
147

answered Feb 27 '12 at 05:34

jyotiprakash

2,086
1
20
26

both of these tables contains the same example person_key, and when i run the query. mysql say's unknown column person_key. – Jetoox Feb 27 '12 at 05:40

score 0 · Answer 5 · answered Feb 27 '12 at 06:53

0

While using the UNION query, you may also want to add indexes to any columns that you are using to join and filter which will improve performance

answered Feb 27 '12 at 06:53

Stephen Senkomago Musoke

3,528
2
29
27

Mysql selecting from two tables

5 Answers5

UPDATE

Linked