Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

SQL - Nested Queries and Pair making

I have the following Query and I have been struggling to solve it. it's not really a homework assignment I just want to know how to do this in order to solve similar question on my homework. The difficult part for me is after i find matching project ids how do i connect them with the employee pairs.
I'd appreciate any help.
This is the question:

I need to show the names of the employees who work in the same project.
The result set should be pairs of employee names and project name,such as:
'name1 name2 project1' , 'name1 name3 project1' etc.
The result set should list only unique and real pair records.
For example,it shouldn't include either 'name2 name1 project1'
or 'name1 name1 project1' records.

Using This DB Scheme:

Projects(ProjectID,ProjectName)
Tasks(ProjectID,Progress,AssignedTo,IsDeleted)
Users(UserID,UserName,UserEmail,DateCreated)
0
dali_dal
Asked:
dali_dal
  • 2
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
AssignedTo field is related to UserID?
Why do you need a pair of names? How about if there's more than two users assigned to a Project? Or only one?
0
 
dali_dalAuthor Commented:
Yes the AssignedTo is related to UserID, that's the connection between the two table (users and tasks). if there's like 3 users on the same project so it can be as follows:
user1 user2 project1
user1 user3 project1

BUT it can't be also 'user2 user1 project1' or 'user3 usre1 project1'
another option is: 'user2 user3 project1' and 'user1 user2 project1'

(SEE THE EXAMPLE I GAVE IN THE ORIGINAL QUESTION).

I have to have pairs, that is what I'm practicing on right now, I want to know how to make these pairs with using union,intersection,nested quesries ect.

Thanks again for any help.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Well, see if this is what you want:

SELECT Tasks2.user2name, users.username, projects.projectname
FROM Users
    INNER JOIN Tasks
        INNER JOIN Projects ON (Projects.ProjectID = Tasks.ProjectID)
        INNER JOIN (SELECT MIN(username) AS user2name, ProjectID
                          FROM Tasks
                              INNER JOIN Users ON (users.UserID = Tasks.AssignedTo)
                          GROUP BY ProjectID) Tasks2
        ON (Tasks2.ProjectID = Tasks.ProjectID)
    ON (users.UserID = Tasks.AssignedTo)
WHERE Tasks2.user2name <> users.username
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now