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'
union if you do not want replicates where
I don't know that which you mean by merge, however, you can [cde] the outcomes:
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.