Basically have two tables in mysql which have similar posts...

TABLEA
  id
  name
  somefield1

TABLEB 
  id
  name
  somefield1
  somefield2

How do you structure a Choose statement to ensure that I'm able to Choose from both tables concurrently, and also have the result sets merged for that posts that are identical?

So for instance, I'm wishing to behave like...

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

...and also have the title, and somefield1 posts from both tables merged together within the result set.

Thank-you for the help!

Sample output appended because question unclear:

I would like the rows from table1 and also the rows from table2 appended within the resultset. For instance when the 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

You are able to mix posts from both tables using (id,title) because the joining 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 wish to join on only the (id) and mix the title and somefield1 posts:

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 this can be a rather unusual method of doing things. I suppose you've your reasons however :-)

If I have misinterpreted your question and you simply desire a more conventional union of these two tables, use something similar to:

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 mix rows and can rather just append the rows in the two queries. Use union by itself if you wish to remove duplicate rows but, if you are certain you will find no replicates or you wouldn't want them removed, union all is frequently more effective.


According to your edit, the particular query could be:

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 replicates where a.name==b.name=='zoot' and a.somefield1==b.somefield1).

I don't know that which you mean by merge, however, you can [cde] the outcomes:

UNION

Would you possibly mean

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

Based on that which you mean by merge, here is a possible solution.

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

Will help you to show is a result of both tables using the results merged into 1 table.