Query multiple tables in SQL Server

Question

I have 3 tables Manager, Lead, Trainee and the way they are related through a Relation table.

Attached image depicts the tables with saple rows and columns :

I need to get the Lead name and Trainee name for a given Manger.Id.

I have got the parameter name from the below query:

select 
    L.Name
from 
    Lead L, Manager M, Relation R 
where 
    R.PrimaryId = M.Id 
    and R.SecondaryId = L.Id 
    and M.Id = 'M101';

How do I get the trainee name apart from the lead name?

What do you want to achieve because according to your question you simply could just select Name from Trainee — itwasntme
– itwasntme, Commented Aug 24, 2015 at 9:34
@itwasntme I need to get the Lead name and Trainee name for a given Manger.Id. — user1168608
– user1168608, Commented Aug 24, 2015 at 9:36
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged — marc_s
– marc_s, Commented Aug 24, 2015 at 10:00

Andomar · Accepted Answer · 2015-08-25 07:42:18Z

3

Your query is a bit clearer if you write it using joins instead of the old pre-1992 syntax:

select  L.Name
from    Manager M
join    Relation R 
on      R.PrimaryId = M.Id
join    Lead L
on      R.SecondaryId = L.Id
where   M.Id = 'M101'

You can add two new left joins to show the trainees per manager:

select  L.Name as lead
,       R.Name as trainee
from    Relation ML
join    Lead L
on      ML.SecondaryId = L.Id
left join
        Relation LT
on      LT.PrimaryId = L.Id
left join
        Trainee T
on      LT.SecondaryId = T.Id
where   ML.PrimaryId = 'M101'

edited Aug 25, 2015 at 7:42

answered Aug 24, 2015 at 9:40

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1168608 Over a year ago

I ned the query to return two columns Lead name and Trainee name.

Andomar Over a year ago

Added a query that does that. It only makes sense if there's no more than one trainee and no more than one lead per manager.

user1168608 Over a year ago

Yes , with this criteria the query works , but at an instance, a manager can have a lead without a trainee. The query gives wrong results for this criteria.

Andomar Over a year ago

Aah, so the trainee is related to the lead, not the manager, I've updated the query.

Collectives™ on Stack Overflow

Query multiple tables in SQL Server

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related