SQL join row not possible “Invalid column name” but name is correct [closed]

by

I would like to execute a query in a SQL Server database. I have two tables

1. dbsCase_activity

From this table, I need columns due_date and follow_up_date

2. dbsCase_interval

From this table, I need id and end_date

I have written a small piece of code for this, which works except for the end_date. When I add the end_date, I get the error:

Invalid column name ‘end_date’

But on the screenshot you can see that there is a column end_date and it is also written correctly:

screenshot

Do you have an idea how I can solve this?

SELECT 
    a.due_date, 
    a.follow_up_date, 
    i_due.id AS due_date_id, 
    i_follow_up.id AS follow_up_id
  --i_end_date.id AS end_date_id
FROM 
    [dbscasemanager_current].[dbo].[dbsCase_activity] a
LEFT JOIN 
    [dbscasemanager_current].[dbo].[dbsCase_interval] i_due ON a.due_date = i_due.id
LEFT JOIN 
    [dbscasemanager_current].[dbo].[dbsCase_interval] i_follow_up ON a.follow_up_date = i_follow_up.id
--LEFT JOIN [dbscasemanager_current].[dbo].[dbsCase_interval] i_end_date ON a.end_date = i_end_date.id

I tried the commented part from the code, that does not work

  • Have you looked into the table to tripple check that column exists in that database? Maybe try MySQL Workbench to graphically poke around in there and see?

    – 

  • @easleyfixed yes, see here -> i.stack.imgur.com/GnS3U.png

    – 

  • Please show tables structure as DDL, not picture. Also it’s not looks as MySQL, probably MSSQL used

    – 

  • 1

    You’re mixing your columns / tables. i_end_date ON a.end_date = i_end_date.id but [dbsCase_activity] a

    – 

  • 1

    dbsCase_interval From this table, I need id and end_date – you already join this table twice, why do you need another join to get end_date, you can already get it. it’s almost like you don’t know your own data, but surely noone else can figure out what you actually want to do

    – 

The screenshot shows the dbsCase_**interval** table. I expect the error refers to the a.end_date expression in the final (currently commented) ON clause, which uses an end_date column from the dbsCase_**activity** table.
(emphasis added)

Are you sure the dbsCase_activity table has a column named end_date that would match up with an id column in dbsCase_interval?

Are you sure any of the columns with _date as part of the name in dbsCase_activity will match up with the id column in dbsCase_interval, which looks to be a simple integer?

Leave a Comment