CODEWITHSUNDEEP
sql
Sachin HR
Sachin HR

Reputation: 460

SQL Multiple Joins Query

Here I have two tables committee_colleges and colleges. Structure of tables is something like this

committee_colleges

    committeeCollegeId collegeId committeeMemberId
    1                  2         1
    2                  2         2 
    3                  3         2

I am storing committeeMemberId from committeeMember table.And one college can have multiple committee Members.How can I wite a query to display only the colleges assigned to specific committee Member. For Example,if committeeMember by id=2 has logged in I want to display colleges by id=2,3.

In college table I have like this,

    collegeId typeName
    1         AICTE
    2         NCTE
    3         NTCS

This is Committee Member table

    committeeMemberId name
    1                 xyz
    2                 abc

Now I am writing something like this,but i know its wrong because I dont know how to take it from College table since I am displaying College details.

SELECT cc.committeeCollegeId as committeeCollegeId,
       c.collegeId as collegeId,
       cc.committeeMemberId as committeeMemberId
FROM committee_college as cc
left outer join College as c
    on cc.collegeId = c.collegeId
where cc.committeeMemberId=:committeeMemberId
order by cc.committeeCollegeId asc

Can anyone tell how to display colleges based on its assignment to particular committeeMember?

Upvotes: 0

Views: 76

Answers (4)

Horia
Horia

Reputation: 1612

You were close, you need INNER JOIN instead of LEFT JOIN:

SELECT  DISTINCT C.typeName --<<== put here all the columns that you want in output
FROM    committee_colleges CC
        INNER JOIN college C
            ON C.collegeId = CC.collegeId
WHERE   CC.committeeMemberId = 2 --<<== your input parameter

EDIT: added DISTINCT

Hope it helps.

Upvotes: 1

sa-es-ir
sa-es-ir

Reputation: 5042

try this:

DECLARE @LoginCommitteeMemberId INT=2
SELECT t2.Name AS MemberName,
       t3.TypeName AS CollageName
  FROM committee_college t1
   INNER JOIN Committee_Member t2 
    ON t1.committeeMemberId = t2.committeeMemberId 
  INNER JOIN College as t3 
    ON t1.collegeId = t3.collegeId 

  WHERE t1.committeeMemberId = @LoginCommitteeMemberId

Upvotes: 0

Suraj
Suraj

Reputation: 3127

You can use simple inner join for that,

If you want collegename based on memberId use following query,

select a.collegeid,a.typeName from
college a, committee_colleges b, committe_member c
where a.collegeid = b.collegeid and
b.committeememberid = c.committeeMemberId
and c.committeeMemberId = '2'

If you want collegename based on committemember name then use following query,

select a.collegeid,a.typeName from
college a, committee_colleges b, committe_member c
where a.collegeid = b.collegeid and
b.committeememberid = c.committeeMemberId
and c.Name = 'xyz'

Hope it will help.

Upvotes: 0

Aman Prajapati
Aman Prajapati

Reputation: 157

You can use below sql statement for the same

DECLARE @committeeMemberId INT = 2  -- Id of Committee member

;WITH CTE_MemberCommittee AS
(
    SELECT  CollegeId 
    FROM    committee_colleges
    WHERE   committeeMemberId = @committeeMemberId
)

SELECT  collegeId, typeName
FROM    college
WHERE   collegeId IN (SELECT CollegeId FROM CTE_MemberCommittee)

Upvotes: 0

Related Questions