SQL – join three table with “where” clause

I want to write an SP that I will use in my c# program. I have three tables: Table1 (id pk, name, …), Table2 (id PK, name, …) and Table3((idT1,idT2) PK FK). So, Table3 models the n:n relationship between Table1 and Table2.

I want to retrieve all the Table2.name(s) related to a single Table1.name.

I have already tried to write a query with two inner join

    CREATE PROCEDURE slct
    @name nvarchar(50)

    AS
    BEGIN
    SET NOCOUNT ON;
    SELECT Table2.name from Table2  
    join Table3 on Table2.id = Table3.idT2 join Table1 on Table1.id = Table3.idT1 where Table1.name = '%'+@name+'%'

    END
    GO

Table1
|1|abc|
|2|def|

Table2
|1|xyz|
|2|mno|

Table3
|1|1|
|1|2|
|2|2|

As result, I see more records than are needed. I expected a list of Table2.name related to a single Table1.name (specified by @name parameter)

sorry for my english.

Answer

select Table2.Names from Table2 
inner join 
     (select Table3.idT1 as SubQueryID1, Table3.idT2 as SubQueryID2 
      from Table3 inner join Table1 on Table1.id = Table3.idT1) 
on 
Table2.Id = SubQueryID2

Advertisement

Answer