Search code examples
sqlsubqueryright-join

SQL right join with functional condition


I have such join:

             "SELECT ADB.column1, BDB.column2 FROM ADB RIGHT JOIN BDB ON  
        (SELECT SUBSTRING( ADB.column1, LEN(ADB.column1) - 
     CHARINDEX('|',REVERSE(ADB.column1)) + 2  ,
 LEN(ADB.column1)  ) FROM BDB) = BDB.column2", sqlCon);

When I run this code I get error

System.Data.SqlClient.SqlException: 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,

, >= or when the subquery is used as an expression.'

How can I force subquery to get only 1 data per row ?


Solution

  • i think you query will be like below

    SELECT ADB.column1,
             BDB.column2 FROM
            ADB RIGHT JOIN BDB ON  
                ( SUBSTRING( ADB.column1, LEN(ADB.column1) - 
             CHARINDEX('|',REVERSE(ADB.column1)) + 2  ,
         LEN(ADB.column1) ) 
           ) = BDB.column2
    

    you are joining ADB and BDB but on join condition used select from BDB which does not make any sense