Union query problem

[andreei87]

Hi guys ... i was wondering if any of you can help me on a little problem i have ... i need to build a SQL UNION query using SELECT, UNION / UNION ALL, WHERE, GROUP BY (no INNER JOIN like the design view in MS Access does). I've been trying to do it for the last few weeks but nothing!



Here are my tables:
Employee: id_employee, salary
penalty: id_employee, penalty, date_of_penalty

I need to take the 2 tables and create a query to calculate the final salary final_salary=salary-penalty and print the results in a new table with the structure: Table:id_employee, salary, penalty, date_of_penalty, final_salary.


if any of you can help me i would be very thankful!

[pbaldy]

Based on the desired result, you don't want a UNION query, you just want to join the two tables.

SELECT...
FROM Employee LEFT JOIN Penalty ON Employee.id_employee = Penalty.id_employee

[andreei87]

My professor wants a UNION query and the problem is worth 2 points on the exam!

[pbaldy]

Ah; well, you don't want me doing it for you, so I'll simply say that you'll need some alias fields in your UNION:

SELECT Employee.id_employee, Employee.salary, 0 as Penalty, 0 As PenaltyDate
FROM Employee
UNION ALL
...

I get 2 points; see attached.

[andreei87]

here is what i have so far but in the final table i get the values 2 times for the employee who has penalty.


SELECT table.id_employee, table.salary, table.penalty, table.penaltydate, table.[salary]-[penalty] AS final_salary
FROM (

SELECT id_employee, salary, 0 as penalty, 0 As penaltydate
FROM employee
UNION ALL
SELECT id_employee, 0 as salary, penalty, penaltydate
FROM Penalty

) AS table
GROUP BY table.id_employee, table.salary, table.penalty, table.penaltydate;

[pbaldy]

Sure; you're grouping by every field.