orthodox4ever orthodox4ever - 4 years ago 121
SQL Question

SQL: Is it possible to add simple custom column in select if there are other tables joined?

If I have query with just one table, adding custom column works.

SELECT
[msg].[MessageTo],
[msg].[MessageFrom],
[msg].[SendTime],
[msg].[ReceiveTime],
[msg].[id],
'2' AS source
FROM
[SMSServer_1].[dbo].[MessageIn] AS [msg]
WHERE
msg.id NOT IN (
SELECT
fk_poruka
FROM
Tekijanka.dbo.crm_poruka
WHERE
fk_status <> 1
)
ORDER BY
[SendTime] DESC


But, if I join some other tables, I get this error:


20018 Invalid column name '2'.


Here is query example

SELECT
[msg].[MessageTo],
[msg].[MessageFrom],
[msg].[SendTime],
[msg].[ReceiveTime],
[msg].[id],
'2' AS source,
[kat].[id] AS [CategoriId],
[kat].[naziv] AS [CategoriName]
FROM
[SMSServer_1].[dbo].[MessageIn] AS [msg]
LEFT JOIN [Tekijanka].[dbo].[crm_poruka] AS [por] ON [por].[fk_poruka] = [msg].[id]
AND [por].[fk_source] = [2]
LEFT JOIN [Tekijanka].[dbo].[crm_kategorije_poruka] AS [kat] ON [kat].[id] = [por].[fk_kategorija]
WHERE
msg.id NOT IN (
SELECT
fk_poruka
FROM
Tekijanka.dbo.crm_poruka
WHERE
fk_status <> 1
)
ORDER BY
[SendTime] DESC


Is there any way to fix it?

Answer Source

The problem is in LEFT JOIN, not in the SELECT section:

AND [por].[fk_source] = [2]

This condition tries to join fk_source and column named [2] - using square m. Of course, there's no such column in tables MessageIn and crm_poruka. You have to change this part of the code (remove the condition or change it to AND [por].[fk_source] = 2).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download