CODEWITHSUNDEEP
sqlderby
fm_strategy
fm_strategy

Reputation: 201

Combine 2 select statements result sets

Is there a way to combine the results from 2 select statements in sql? (like Union operator does - but Union works only for same number and type of columns)

I want to do something like

SELECT * FROM JOBS j 
UNION 
SELECT COUNT(fj.Id_Filter) FROM JOBS j LEFT JOIN FILTER_JOBS fj ON j.Id=fj.Id_Job

but it doesn't work with UNION

Upvotes: 3

Views: 155

Answers (1)

Robert
Robert

Reputation: 25753

AlL of the select statements must have the same count of columns, you can do it by adding null value column with aliases for example: 

SELECT cast(null as int) as cnt,col1,col2 FROM JOBS j 
UNION 
SELECT COUNT(fj.Id_Filter),null,null FROM JOBS j LEFT JOIN FILTER_JOBS fj ON j.Id=fj.Id_Job

You need to put aliases in 1st select statement with cast/converting. In 2nd statement you need include the same count of columns as 1st with null.

EDIT:

SELECT cast(null as int) as cnt,j.* FROM JOBS j 
UNION 
SELECT COUNT(fj.Id_Filter),j.* FROM JOBS j LEFT JOIN FILTER_JOBS fj ON j.Id=fj.Id_Job

Upvotes: 5

Related Questions