MySql SELECT union for different columns? [duplicate]

Question

I have a select query that selects files with a thumbnail file attached and I also need to get those with no thumbnail attached.

My current sql query is

SELECT   node.title, node.nid, files.fid, files.filepath,           content_type_mobile_event.field_date_value,           content_type_mobile_event.field_movie_shorts_value,           content_type_mobile_event.field_director_value,           content_type_mobile_event.field_length_value,           content_type_mobile_event.field_movie_type_value,           content_type_mobile_event.field_year_value,           content_type_mobile_event.field_event_type_value,           content_type_mobile_event.field_movie_location_value,           content_type_mobile_event.field_movie_desc_value,           content_type_mobile_event.field_movie_id_value,           content_type_mobile_event.field_movie_thumb_fid,           content_type_mobile_event.field_movie_trailer_url  FROM     node, content_type_mobile_event, files  WHERE    node.nid=content_type_mobile_event.nid AND           content_type_mobile_event.field_movie_thumb_fid=files.fid  ORDER BY content_type_mobile_event.field_date_value ASC 

I need to also get

SELECT   node.title, node.nid, content_type_mobile_event.field_date_value,           content_type_mobile_event.field_movie_shorts_value,           content_type_mobile_event.field_director_value,           content_type_mobile_event.field_length_value,           content_type_mobile_event.field_movie_type_value,           content_type_mobile_event.field_year_value,           content_type_mobile_event.field_event_type_value,           content_type_mobile_event.field_movie_location_value,           content_type_mobile_event.field_movie_desc_value,           content_type_mobile_event.field_movie_id_value,           content_type_mobile_event.field_movie_thumb_fid,           content_type_mobile_event.field_movie_trailer_url  FROM     node, content_type_mobile_event  WHERE    node.nid=content_type_mobile_event.nid AND          content_type_mobile_event.field_movie_thumb_fid!=1  ORDER BY content_type_mobile_event.field_date_value ASC 

I would normally just do a

( SELECT   node.title, node.nid, files.fid, files.filepath,           content_type_mobile_event.field_date_value,           content_type_mobile_event.field_movie_shorts_value,           content_type_mobile_event.field_director_value,           content_type_mobile_event.field_length_value,           content_type_mobile_event.field_movie_type_value,           content_type_mobile_event.field_year_value,           content_type_mobile_event.field_event_type_value,           content_type_mobile_event.field_movie_location_value,           content_type_mobile_event.field_movie_desc_value,           content_type_mobile_event.field_movie_id_value,           content_type_mobile_event.field_movie_thumb_fid,           content_type_mobile_event.field_movie_trailer_url  FROM     node, content_type_mobile_event, files  WHERE    node.nid=content_type_mobile_event.nid AND           content_type_mobile_event.field_movie_thumb_fid=files.fid  ORDER BY content_type_mobile_event.field_date_value ASC ) UNION ( SELECT   node.title, node.nid, content_type_mobile_event.field_date_value,           content_type_mobile_event.field_movie_shorts_value,           content_type_mobile_event.field_director_value,           content_type_mobile_event.field_length_value,           content_type_mobile_event.field_movie_type_value,           content_type_mobile_event.field_year_value,           content_type_mobile_event.field_event_type_value,           content_type_mobile_event.field_movie_location_value,           content_type_mobile_event.field_movie_desc_value,           content_type_mobile_event.field_movie_id_value,           content_type_mobile_event.field_movie_thumb_fid,           content_type_mobile_event.field_movie_trailer_url  FROM     node, content_type_mobile_event  WHERE    node.nid=content_type_mobile_event.nid AND           content_type_mobile_event.field_movie_thumb_fid!=1  ORDER BY content_type_mobile_event.field_date_value ASC ) 

But the problem is the second one has a different set of columns (minus the files.* part)

I cannot for the life of me figure out how to do this.

Answer 1

If you have different fields that have different meaning too, you can't and shouldn't return them in the same position. You can however 'fill in the blanks' by adding null to your fields, like this:

select id, name, date, null as userid, 'A' as recordtype from table1 union all select id, name, null /*as date*/, userid, 'B' as recordtype from table2  

You can provide an alias for the null in the first select. You can add aliases in the second select for clarity, but it won't be used. You can even use constant values which you can use to distinguish the record type later.

Answer 2

If select A does not have a column, simply use a null value

table A (colA, ColB, ColC)  table B (colA, ColD, ColE)  select colA, ColB, ColC, null, null from table A union select colA, null, null, colD, colE from table B 

just need to be sure that each column you are matching are of the same data type