mysql union different number of columns

Question

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?

Answer 1

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

Answer 2

Add empty columns (null as columnName) to the query that has fewer columns. You should avoid using * in this case, to have better control on order of columns in both queries.

Answer 3

Another very dodgy way to get around the issue is to CONCAT_WS columns not shared between the tables e.g.

SELECT `r_id`, `r_added`, CONCAT_WS('###',`otherfield1`,`otherfield2`) as r_other FROM table1
UNION
SELECT `r_id`, `r_added`, CONCAT_WS('###',`otherfield3`,`otherfield4`,`otherfield5`) as r_other FROM table2

You can then programmatically reconstruct that data e.g.

$rother = explode("###", $row['r_other']);

Very bad way to get at this data but might get someone out of a fix. You could even change the separator e.g. #t1# #t2# search the string for that to workout what table the concatenated data is from.