SQL Inner join not allowed in same table

In the following query I am trying to create 2 inner join for rs1 and rs2
however in sql server i have only the possibility to select rr1.ResourceId=rs2.Id
but i cannot make rr1.ResourceId=rs1.Id

can you explain me what i should do ?

  • Does “select for update” prevent other connections inserting when the row is not present
  • How cascade Update/Delete works internally in SQL Server?
  • SQL query help, conditional join
  • Delete “duplicate” rows in SQL Server 2010
  • How to get leading zeroes with DatePart?
  • SQL Server 2012, only show one row if other exists
  • select r.OwningTerritoryId ,rs1.ISRC , rs2.ISRC 
    from Resource rs1 , Resource rs2
        inner join ReleaseResource rr1 
           on rr1.ResourceId=rs2.Id
    

    Thanks.

    2 Solutions collect form web for “SQL Inner join not allowed in same table”

    You can cerate as many inner joins as you want …

    select r.OwningTerritoryId ,rs1.ISRC , rs2.ISRC 
    from Resource rs1
        inner join ReleaseResource rr2 
           on rr2.ResourceId=rs1.Id
        inner join ReleaseResource rr2
           on rr3.ResourceId=rs1.Id
        inner join ReleaseResource rr3
           on rr4.ResourceId=rs1.Id
        inner join ReleaseResource rr4
           on rr5.ResourceId=rs1.Id
    

    Although, [generally], you do this for a reason that requires that a different join condition for each join.

    If you want the joins to be between each table in a pair, then between the pairs, then try:

    select r.OwningTerritoryId ,rs1.ISRC , rs2.ISRC 
       from (Resource r1 join ReleaseResource rr1 
                   On rr1.ResourceId = r1.Id)
          join (Resource r2 join ReleaseResource rr2 
                   On rr2.ResourceId = r2.Id)
             On r2.id = r1.id   -- Here put condition for this join between the pairs 
    

    Try like this….

    select r.OwningTerritoryId ,rs1.ISRC , rs2.ISRC from Resource rs1 
    inner join ReleaseResource rr1 on rr1.ResourceId=rs2.Id
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.