Combine Union ALL with Inner Join Statement

Question

I have a problem how to combine the sql statements (inner join and union all. as a Newbie in SQL. Hopefully all members can help me to solve the problems.attached herewith the SQL. The leave_Trx and leave_History contain same values that need to be union. Thank you.

select m.name, t.startdt, t.enddt,t.noday,t.createdDT,
      (case when t.approveST = 'Y' then 'Approved' when t.approveST = 'N' then 'Not Approved' else 'Pending' end) as appST
      from leave_Trx t
      inner join leave_MType m on m.typeID = t.trxID
      inner join hr_personaldata b on b.pers_ID = @pers_ID
      where year(t.startdt) = @yyear
      and b.pers_ID = @pers_ID and b.pers_name LIKE '%'+@pers_name+'%'
      and b.pers_compID LIKE ''+@compID+''
      union all
      select * from leave_History h
      where year(h.startdt) = @yyear and h.status = 'A'
      ORDER BY t.startdt

Does the `leave_History` table have six columns? What is your current output/error? — Tim Biegeleisen, Aug 23 '16 at 03:25
Don't use `where year(h.startdt) = @yyear`. See http://stackoverflow.com/questions/14104304/mysql-select-where-datetime-matches-day-and-not-necessarily-time why you shouldn't. — user1032531, Aug 23 '16 at 03:26
the error is "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists." — Rupert Malcolm Jr, Aug 23 '16 at 03:27
You need to also select 6 columns from `leave_History`, and ideally the columns should be the same type in both parts of your `UNION` query (or the types should be coercible into one another). — Tim Biegeleisen, Aug 23 '16 at 03:28
Also, don't use `select *` but write out the columns. Among other benefits, will help you ensure that that all your unions have compatible columns. — user1032531, Aug 23 '16 at 03:31
`leave_Trx` and `leave_History` may both have 23 columns, but it is your SELECT statements which define which are made available, and they must both match. — user1032531, Aug 23 '16 at 03:32

score 0 · Accepted Answer · answered Aug 23 '16 at 03:59

 select  t.pers_ID,t.startdt, t.enddt,t.noday,t.createdDT,
      (case when t.approveST = 'Y' then 'Approved' when t.approveST = 'N' then 'Not Approved' else 'Pending' end) as appST
      from leave_Trx t
      inner join leave_MType m on m.typeID = t.pers_ID
      inner join hr_personaldata l on l.pers_ID = @pers_ID
      where year(t.startdt) = @yyear and t.status = 'A'
      union all
      select h.pers_ID, h.startdt, h.enddt,h.noday,h.createdDT,
      (case when h.approveST = 'Y' then 'Approved' when h.approveST = 'N' then 'Not Approved' else 'Pending' end) as appST 
      from leave_History h
      inner join leave_MType m on m.typeID = h.pers_ID
      inner join hr_personaldata b on b.pers_ID = @pers_ID
      where year(h.startdt) = @yyear and h.status = 'A'

Combine Union ALL with Inner Join Statement

1 Answers1