SELECT with left join and union

Question

I have this query that return invoices made on a certain employee shift

SELECT
i.dateTime,i.amount,i.totalProfit,i.shiftID,
i_o.itemID,i_o.quantity,
item.name itemName,
p.full_name

from invoice i 

LEFT JOIN 
inv_order i_o on i_o.invID=i.invID

LEFT JOIN 
`item-service` item on item.itemID = i_o.itemID


LEFT JOIN person p on 
p.PID=i.personID

where i.shiftID =97

but then, i need to get the employee name from the employee table, and i only have the shiftID.

SELECT
i.dateTime,i.type,i.amount,i.totalProfit,i.shiftID,i_o.itemID,i_o.quantity,item.name itemName,p.full_name

from invoice i 

LEFT JOIN 
inv_order i_o on i_o.invID=i.invID

LEFT JOIN 
`item-service` item on item.itemID = i_o.itemID


LEFT JOIN person p on 
p.PID=i.personID

where i.shiftID =97

UNION

SELECT e.name from employee e

left join shift s on s.empID = e.empID

where s.shiftID =97

the mysql return this error

The used SELECT statements have a different number of columns

Does this answer your question? [The used SELECT statements have a different number of columns (REDUX!!)](https://stackoverflow.com/questions/3655708/the-used-select-statements-have-a-different-number-of-columns-redux) — philipxy, Aug 16 '22 at 06:02

score 1 · Accepted Answer · answered Nov 19 '18 at 00:49

The error message is quite clear as to the problem: your first query returns 8 columns of data and your second only 1, so you can't UNION them. It seems you probably want to just JOIN the employee table via the shift table e.g.

SELECT i.dateTime
      ,i.type
      ,i.amount
      ,i.totalProfit
      ,i.shiftID
      ,i_o.itemID
      ,i_o.quantity
      ,item.name AS itemName
      ,p.full_name
      ,e.name
FROM invoice i 
LEFT JOIN inv_order i_o ON i_o.invID=i.invID
LEFT JOIN `item-service` item ON item.itemID = i_o.itemID
LEFT JOIN person p ON p.PID=i.personID
LEFT JOIN shift s ON s.shiftID = i.shiftID
LEFT JOIN employee e ON e.empID = s.empID
WHERE i.shiftID = 97

SELECT with left join and union

1 Answers1