The multi-part identifier could not be bound in SQL

Question

Below is my query where i want to check whether record from TABLE A is being used in TABLE B and based on that i am returning 'Y' and 'N'.

SELECT DISTINCT 
    ca.ID,
    IF(da.AM_SYSID IS NULL, 'Y', 'N')
FROM 
    TABLEA ca
LEFT JOIN 
    TABLEB da ON ca.ID = da.AM_SYSID

but I am getting an error

The multi-part identifier could not be bound. searched for solution

I'm still unable to see my mistake.

Because `IF` is a statement that you use for control flow, whereas `IFF` is an operator taking a boolean and returning one of two possible results. Since that seems to be what you're trying to do, it looks like you're just using the wrong word here. — Damien_The_Unbeliever, Jul 10 '18 at 07:29

score 5 · Accepted Answer · edited Jul 10 '18 at 08:19

The problem is your IF command.

In SQL Server, IF is not a function, ie something that you give two arguments and a condition and it returns the appropriate argument. Instead, it evaluates the condition and starts running a new statement. This is not what you want.

The function equivalent is case. Thus, what you really want(supposing your query is correct) is this:

SELECT DISTINCT 
    ca.ID ,
    CASE 
       WHEN da.AM_SYSID IS NULL 
          THEN 'Y' 
          ELSE 'N' 
    END
FROM 
    TABLEA ca
LEFT JOIN 
    TABLEB da ON ca.ID = da.AM_SYSID

score 1 · Answer 2 · edited Jul 10 '18 at 08:20

1

I believe your problem is in IF statement. You need either to use CASE/WHEN or IIF.

Try this:

SELECT DISTINCT 
    ca.ID ,
    IIF(da.AM_SYSID IS NULL, 'Y', 'N') AS YourId
FROM 
    TABLEA ca
LEFT JOIN 
    TABLEB da ON ca.ID = da.AM_SYSID

More details: https://stackoverflow.com/a/63480/2524304

edited Jul 10 '18 at 08:20

marc_s

704,970
168
1,303
1,425

answered Jul 10 '18 at 07:27

Maxim Zhukov

9,875
5
39
82

score 1 · Answer 3 · answered Jul 10 '18 at 11:29

A much better way to write this query is to use case and exists:

SELECT ca.ID,
       (CASE WHEN NOT EXISTS (SELECT 1
                              FROM TABLEB da 
                              WHERE ca.ID = da.AM_SYSID
                             )
             THEN 'Y' ELSE 'N'
        END)
FROM TABLEA ca;

Why is this better? SELECT DISTINCT incurs overhead for removing the duplicates. This version has no such overhead (assuming that ca.id is unique, a reasonable assumption for an id).

The multi-part identifier could not be bound in SQL

3 Answers3