SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    two queries without using UNION

    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

    Code:
     
           $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. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    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?
    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. #4
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    This worked.
    Thank everyone for the effort!

    Code:
    $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. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

    Thanks for the valuable comment


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •