Geek Answers Handbook

In mySQL, is it possible to select SELECT from two tables and join columns?

If I have two tables in mysql that have similar columns ...

TABLEA id name somefield1 TABLEB id name somefield1 somefield2

How to structure a SELECT statement so that I can SELECT from both tables at the same time, and so that the result sets are combined for the same columns?

So, for example, I hope to do something like ...

 SELECT name, somefield1 FROM TABLEA, TABLEB WHERE name="mooseburgers";

... and the names and columns of somefield1 from both tables are combined into a result set.

Thanks for the help!

An example output is added because the question is fuzzy:

I need rows from table1 and rows from table2 added to the result set. For example, if tables contain

 TABLEA id(1) name(zoot) somefield(suit) TABLEB id(1) name(zoot) somefield(flute) The resultet would look like: name | somefield1 zoot suit zoot flute
+7
sql database mysql
source share
4 answers

You can combine columns from both tables using (id, name) as join criteria with:

 select a.id as id, a.name as name, a.somefield1 || ' ' || b.somefied1 as somefield1 from tablea a, tableb b where a.id = b.id and a.name = b.name and b.name = 'mooseburgers';

If you want to join only (id) and combine the name and somefield1 columns:

 select a.id as id, a.name || ' ' || b.name as name, a.somefield1 || ' ' || b.somefied1 as somefield1 from tablea a, tableb b where a.id = b.id and b.name = 'mooseburgers';

Although I must admit that this is a rather unusual way of doing things. I assume you have your reasons :-)

If I misunderstood your question and you just need a more traditional join of the two tables, use something like:

 select id, name, somefield1, '' as somefield2 from tablea where name = 'mooseburgers' union all select id, name, somefield1, somefield2 from tableb where name = 'mooseburgers'

This will not concatenate the rows, but instead just add the rows from the two queries. Use union yourself if you want to remove duplicate rows, but if you are sure that there are no duplicates or you do not want to delete them, union all often more efficient.

Based on your edit, the actual query will look like this:

 select name, somefield1 from tablea where name = 'zoot' union all select name, somefield1 from tableb where name = 'zoot'

(or union if you do not want to duplicate, where a.name==b.name=='zoot' and a.somefield1==b.somefield1 ).

+7
source share

I'm not sure what you mean by merging, but you can UNION results:

 SELECT id, name, somefield1 FROM TABLEA WHERE name="mooseburgers" union all SELECT id, name, somefield1 FROM TABLEB WHERE name="mooseburgers";
+2
source share

Perhaps you mean

 SELECT tableA.id, tableA.name, tableA.somefield1 FROM tableA, tableB WHERE tableA.name = tableB.name AND tableA.name="mooseburgers"
0
source share

Depending on what you mean by merging, here is a possible solution.

 Select id,name,somefield1 from TableA WHERE Name ='mooseburgers' UNION Select id,name,somefield1 from TableB WHERE Name ='mooseburgers'

Allows you to show results from both tables with results combined into 1 table.

0
source share

More articles:


All Articles