How to make this query?

[stirrah]

Hey!

 

I have these tables:

 

Project:

name - name of project

creatorId - id which is related to id 'userId' in table called Users. This table also have name of user and surname of user.

statusId - id which is related to id 'statusId' in table called Status. This table also have Started, Ended, Closed.

assigneId - id which is related to id 'userId' in table called Users. This table also have name of user and surname of user. (relation same as creatorId).

 

I think I have given you everything relevant. 

Now to my question.. How can I query this so I can get all information I want?

 

I'm getting what I want really, but problem is that I want name of creatorId and assigneId, not just ID. How can I do this in 1 query?

 

[Barand]

Connect twice to the User table with different table aliases

SELECT p.name as ProjectName
    , p.statusId
    , p.creatorId
    , u1.name as CreatorName
    , u1.surname as CreatorSurname
    , p.assigneId
    , u2.name as AssigneName
    , u2.surname as AssignSurname
FROM
    project p
    INNER JOIN Users u1 ON p.creatorId = u1.userId
    INNER JOIN Users u2 ON p.assigneId = u2.userId

Edited October 24, 2013 by Barand

[stirrah]

Thats GREAT! Thanks Barand.

 

I have another question, is it ok to ask it here or should I make a new thread?

I'll ask it here and if its wrong I'll move it.

 

I also have a table called IsAssigned. This table connects userId and projectId. This helps me check who is assigned to a project. So 1 project can have many assignes, ok?

 

Now I want to get all assignes that is assigned to a project, say projectId 5 for example. How can I do this? I have tried but im not gettng it right. I HAVE succeeded though, but I know my solution is bad. How would you make it?

[Barand]

If you have that isAssigned table then the assigneId in the project table becomes redundant (however I left it in)

SELECT p.name as ProjectName
    , p.statusId
    , p.creatorId
    , u1.name as CreatorName
    , u1.surname as CreatorSurname
    , p.assigneId
    , u2.name as AssigneName
    , u2.surname as AssignSurname
    , GROUP_CONCAT(CONCAT(u3.name, ' ', u3.surname) SEPARATOR ', ') as Assignees
FROM
    project p
    INNER JOIN Users u1 ON p.creatorId = u1.userId
    INNER JOIN Users u2 ON p.assigneId = u2.userId
    LEFT JOIN isAssigned a ON p.projectId = a.projectId
    LEFT JOIN Users u3 ON a.userId = u3.userId
WHERE p.projectId = 5
GROUP BY p.projectId

The other way is to take out the grouping and create a row for each assignee with project info repeated in each row. I used LEFT JOIN for the cases where no-one has been assigned yet.

Edited October 25, 2013 by Barand

[stirrah]

Thanks for fast reply. That seems advanced.. hehe  

 

Is this wrong? Im just trying to learn.

SELECT users.first_name,users.last_name,project.projektName 
FROM users, project, isProjectAssigne 
WHERE users.user_Id = isProjectAssigne.userId AND project.projectId = isProjectAssigne.projectId

[Barand]

That should work but I'd recommend using explicit JOIN...ON rather than just listing the tables and using the WHERE clause to define the relations.

eg

SELECT u.first_name, u.last_name, p.projektName
FROM project p
    LEFT JOIN isProjectAssigne pa ON p.projectId = pa.projectId
    LEFT JOIN users u ON u.user_Id = pa.userId

1. You can only do INNER JOINS (default) using that method.
2. The explicit JOIN ON format separates the structure of the query from the selection criteria in the WHERE clause.
3. I also prefer to use short table aliases as IMHO using full table names makes the query cluttered and  harder to read

I have used LEFT JOINS for the users so that projects with no one assigned are also listed. With an INNER JOIN you would only see projects with assigned users.