CODEWITHSUNDEEP
sqlsql-server-2005
Hukam
Hukam

Reputation: 1026

join using two column in sql

    ProjectMaster
     ProjectID ITManagerID DevelopmentManagerID

--------------------------------------------------
        1         1000         1001

     UserTable

     UserID UserName
     ----------------
      1000   Sam
      1001   Ram

    Result 
      Project   ItManagerName DevManagerName
------------------------------------------------
        1          sam          ram

Help to write query

Edit: I tried

select
    projectid,projectName,projectdescription,startdate,enddate,
    apsmanagerid,projectmanager,ragstatus,projectstatus,projectpriority,
    categoryid,inactivedate,comments,it.userName AS ITProjectManagerName,
    dev.userName as DevManagerName
from pmis_project p,pmis_user It,pmis_user dev
where p.DevprojectManager = It.userid
  and p.ITmanagerid = dev.userid
  and p.projectid IN (Select Projectid from SelectedProject)

Upvotes: 1

Views: 410

Answers (3)

fARcRY
fARcRY

Reputation: 2358

SELECT * FROM ProjectMaster PM INNER JOIN UserTable UT ON UT.UserId = ITManagerId INNER JOIN UserTable UT1 ON UT1.UserId = DevelopmentManagerId

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

You can JOIN a table as many times as needed.

In this case

  • one JOIN to Users to get the ITManager's name.
  • one JOIN to Users to get the DevManager's name.

SQL Statement

SELECT  ProjectID
        , ITManagerName = m.UserID
        , DevManagerName = d.UserID
FROM    ProjectMaster pm
        INNER JOIN UserTable m ON m.UserID = pm.ITManagerID
        INNER JOIN UserTable d ON d.UserID = pm.DevelopmentManagerID

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

You can include the same table multiple times in the FROM clause of a SELECT query. If you're doing this, you frequently want to introduce an alias for one or more of these tables, so that they can be referred to in the rest of the query. You introduce an alias by adding AS alias after the name of the table, e.g.:

SELECT
    *
FROM
    Table As t
WHERE
    t.Column = 'x' --<-- using the alias here

(the AS is actually optional)

Joining tables in a FROM clause is performed by using the JOIN keyword, and placing the conditions for the join in the ON:

FROM
    Table1 t1
        inner join
    Table2 t2
        on
             t1.ColumnA = t2.ColumnB

Upvotes: 1

Related Questions