I know union queries have to have the same number of columns. I'm trying to get results from the table comments and results from the table strings, which has multiple joins. How do i this correctly? I haven't tested yet because i know i'll get an error with different number of columns. Here are the two queries that i'm trying to combine.
query 1(strings)
SELECT sub.actionid as usersub,
ftable.`last-time` as lastvisited, updatetable.recent as mostrecent, parent.* FROM `strings` as parent
LEFT JOIN subscribe sub on sub.actionid=parent.id AND sub.userid=:userid
JOIN followers ftable on ((ftable.sid=parent.sid AND ftable.page='1') OR
(ftable.sid=parent.sid AND ftable.position=parent.position AND ftable.page='0')
OR (ftable.profile=parent.starter AND parent.guideline='1')) AND ftable.userid=:userid
JOIN `update-recent` updatetable on
((updatetable.sid=parent.sid AND updatetable.position=parent.position AND updatetable.pageid='0')
OR (updatetable.profile=parent.starter) OR (updatetable.pageid=parent.pageid AND parent.pageid!=0))
WHERE ftable.userid=:userid AND parent.deleted='0' GROUP BY parent.id
query 2(comments)
SELECT * FROM comments WHERE viewed='0' AND (`starter-id`=:userid OR respondid=:userid)
I'd like to order the results by the timestamp column posted (most recent) ORDER BY posted DESC
How do i union these queries?
解决方案
You would want to select columns as NULL to take up for the empty space in certain tables.
Table A: (id, column1)
Table B: (id, column1, column2)
Select id, column1, null as column2 from tableA
UNION
Select id, column1, column2 from tableB