CODEWITHSUNDEEP
mysqlsql
Hunter McMillen
Hunter McMillen

Reputation: 61512

Union on two selects with a different number of columns

I am trying to add one more column of output to my result set in MySQL. I can easily get these results by doing two separate queries, but I would like them to be in the same result set so I can export them together.

Anyway here is what I am doing:

select buildid, cast(status as char), eventtime from buildstatuses 
where statustype = 8)
union all (select schedulerid, "", "" from builds 
where builds.buildid in (select buildid from buildstatuses where statustype = 8));

I am only getting the columns listed in the initial select as output:

build status eventtime

I don't get the other column I want: schedulerid

any ideas?

Thanks

EDIT: update 

select buildstatuses.buildid, cast(status as char), eventtime, "" as schedulerid
from buildstatuses
inner join builds
on buildstatuses.buildid = builds.buildid
where buildstatuses.statustype = 8;

This is where I am at right now, the column id like to add is still showing up blank, even with the alias

Upvotes: 0

Views: 3612

Answers (1)

Alan Geleynse
Alan Geleynse

Reputation: 25139

select buildid, cast(status as char), eventtime, schedulerid from buildstatuses 
inner join builds on buildstatuses.buildid = builds.buildid
where statustype = 8

UNION will only select the columns that are common between both tables.

What you are looking for is a join.

Edit: Originally I misunderstood the question and suggested a change to the union. I updated the answer to use a join which should give what you are looking for.

Upvotes: 2

Related Questions