Two queries without using UNION

Databases
1

Hello,

  • our HR manager assign Tasks to Employees. Assume one task is assign to one employee only.
  • employee can assign (forward) a task he got from HR to another employee.

Table 1 :

TASKS - contain all tasks

taskid | subject | description | priority | date_created | deadline

Table 2 :

TASKS_EMPLOYEES - task assigned by HR and to whom

taskid | empno

Table 3 :

TASKS_ASSIGNED - tasks assigned by one employee to another.

taskid | from | to | date

  • there can be tasks without assigned (forwrded).

QUESTION :
when a employee loged in to his panel, he should see all the tasks assigned by HR as well as by other employees

QUERY :

note : $username=currently loged in user’s empno

 
       $query="SELECT * FROM tasks 
		LEFT JOIN tasks_employees 
		ON tasks_employees.taskid=tasks.taskid
		WHERE tasks_employees.empno='$username'
          
		UNION
        
		SELECT * FROM tasks 
		LEFT JOIN tasks_assigned 
		ON tasks.taskid=tasks_assigned.taskid
		WHERE tasks_assigned.to='$username'";

the above produces the following error :

The used SELECT statements have a different number of columns

so is there any other method i can use without the UNION to combine the results?

2

Will a task only ever be assigned to one employee or can multiple employees be assigned to a task?

When a task assigned by HR is “passed” to another employee, how many times is the task allowed to be passed from the currently assigned employee to another employee?

3

Good questions!

  • Currently a task is only assigned to one person as per HR When i checked with him.
    but this can be changed at anytime (1 to many). so better prepared.

  • currently only one assignment (forwarding). but better to design the program for further forwardings in furture if require.

4

This worked.
Thank everyone for the effort!


$query="SELECT tasks.taskid,tasks.subject,tasks.task,tasks.dt,tasks.deadline,tasks.priority,tasks.status
        FROM tasks
        LEFT JOIN tasks_employees
        ON tasks_employees.taskid=tasks.taskid
        WHERE tasks_employees.empno='$username'
		

        UNION ALL

        SELECT tasks.taskid,tasks.subject,tasks.task,tasks.dt,tasks.deadline,tasks.priority,tasks.status
		FROM tasks
        LEFT JOIN tasks_assigned
        ON tasks.taskid=tasks_assigned.taskid
        WHERE tasks_assigned.to='$username'
		";
5

just a quick comment

LEFT JOIN is for those instances when you want the rows from the left table returned with or without matching rows from the right table

in both the SELECTs of your UNION query, clearly INNER JOIN is what you want, not LEFT JOIN, because you have a WHERE condition on the right table that will never be NULL

:slight_smile:

6

Thanks for the valuable comment :slight_smile: