SQL - Nested Queries and Pair making

Posted on 2005-04-13
Last Modified: 2008-03-06
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:

Question by:dali_dal
    LVL 44

    Expert Comment

    by:Vitor Montalvão
    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?

    Author Comment

    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'


    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.
    LVL 44

    Accepted Solution

    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

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Trying to set up MySQL Replication 3 64
    wordpress to database 2 49
    VFP9 options in the cloud (and elsewhere) 3 102
    MarkLogic 1 31
    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    Read about achieving the basic levels of HRIS security in the workplace.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now