Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql selecting from two tables

Tags:

sql

mysql

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

like image 759
Jetoox Avatar asked Feb 27 '12 05:02

Jetoox


1 Answers

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

like image 78
mathematical.coffee Avatar answered Oct 16 '22 01:10

mathematical.coffee