Getting Invalid Column Name for a Column that Does Exist?-CodePudding

I'm trying to populate tables in a database with values from another imported table in my database. Before using the INSERT INTO statement, I'm trying to just run the select statement on its own to verify that I'll get the right values, but running into this error. I've read a couple other threads on the same problem but can't figure out what is wrong with mine specifically.

(using Azure Data Studio, if that is useful info). Here is my code (screenshots linked below too) --

        SELECT DISTINCT 
        StateAbbr,
        [State],
        Longitude,
        Latitude,
        Zipcode,
        City,
        [Population]
    FROM BehaviorCoverageMerged b
        JOIN
            (SELECT
                Zipcode,
                City,
                [Population]
            FROM [2010ZipData]) z 
            ON [b].[StateAbbr] = [z].[StateAbbr];
    GO

I keep getting

Msg 207, Level 16, State 1, Line 14
Invalid column name 'StateAbbr'.

The column does exist in the table in my database, so I do not know what the problem is. Does anyone know why this is happening? Attaching two screenshots as well - one of the query & error message, and another one of the table schema (where the column lives). Any help greatly appreciated!

Query & error message Column names in table

CodePudding user response：

It looks like the problem is that the StateAbbr column in the BehaviorCoverageMerged table is not the same data type as the StateAbbr column in the 2010ZipData table. The error message you're seeing, "Invalid column name 'StateAbbr'", is actually a misleading error message that is thrown when the SQL engine is unable to compare two columns because they are of different data types.

To fix this issue, you'll need to make sure that the StateAbbr column in both tables is of the same data type. This can be done using a CAST or CONVERT function to explicitly convert the data type of one of the columns to match the other.

Here's an example of how you could modify your query to use a CAST function to make sure the columns are the same data type:

SELECT DISTINCT 
    StateAbbr,
    [State],
    Longitude,
    Latitude,
    Zipcode,
    City,
    [Population]
FROM BehaviorCoverageMerged b
    JOIN
        (SELECT
            Zipcode,
            City,
            [Population],
            CAST(StateAbbr AS VARCHAR(10)) AS StateAbbr
        FROM [2010ZipData]) z 
        ON [b].[StateAbbr] = [z].[StateAbbr];

In this example, I'm using the CAST function to convert the StateAbbr column in the 2010ZipData table to a VARCHAR(10) data type, which should be the same data type as the StateAbbr column in the BehaviorCoverageMerged table (assuming that column is a VARCHAR of some length).

CodePudding user response：

You'll need to select StateAbbr from 2010ZipData in the

        SELECT DISTINCT 
        b.StateAbbr,           -- changed
...
                Zipcode,
                City,
                [Population]
                , StateAbbr  -- added
            FROM [2010ZipData]) z

but you should be able to just join without the 2nd select:

        SELECT DISTINCT 
        b.StateAbbr,           -- changed
        [State],
        Longitude,
        Latitude,
        Zipcode,
        City,
        [Population]
    FROM BehaviorCoverageMerged b
            FROM [2010ZipData] z 
            ON [b].[StateAbbr] = [z].[StateAbbr];
    GO