SQL right join with functional condition

Question

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 ?

Which dbms? SQL is just a language use by many dbms vendors. Each vendor has their own syntax. — Eric, Oct 10 '18 at 17:40

score 1 · Accepted Answer · answered Oct 10 '18 at 17:39

1

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

answered Oct 10 '18 at 17:39

Zaynul Abadin Tuhin

31,407
5
33
63

You are right Sir! my mistake. Too complicated query, I've shown only part of it for simplicity. Thank you very much for perfect answer! – David Oct 10 '18 at 17:45
Just did it. There is time interval which you have to wait , till you may mark answer :) – David Oct 10 '18 at 17:51

score 0 · Answer 2 · answered Oct 10 '18 at 17:42

Seem you did confuse yourself... You don't need to subselect FROM BDB since you have all those ADB.column1 transformations.

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

SQL right join with functional condition

2 Answers2