If I have following query
if not exists(select * from DeliveryTemplate where TemplateId=2)
begin
select usersCode, 1,2,'User {UsersCode}',' hello {Username},', null
from User
end
how can I extend this query in order to select one more column DeliveryCode from DeliveryTemplate table?
So you need to use JOIN something like:
if not exists(select * from DeliveryTemplate where TemplateId=2)
begin
select u.usersCode, 1,2,'User {UsersCode}',' hello {Username},', null, dt.DeliveryCode
from User u
left join DeliveryTemplate dt on u.Id = dt.UserId
end
declare @count int
declare @DeliveryCode nvarchar(100)
select @count = count(*),
@DeliveryCode = DeliveryCode
from DeliveryTemplate
where TemplateId=2
if @count = 0
begin
select usersCode,
1,
2,
'User {UsersCode}',
'hello {Username},',
null,
@DeliveryCode AS 'DeliveryCode'
from User
end
This should be done as follows with INNER JOIN:
SELECT usersCode, 1,2,'User {UsersCode}',' hello {Username},', null, DeliveryCode
FROM User
INNER JOIN DeliveryTemplate ON User.ID=DeliveryTemplate.UserId
WHERE TemplateId=2
if not exists(select * from DeliveryTemplate where TemplateId=2)
begin
select usersCode, 1,2,'User {UsersCode}',' hello {Username},', null, DeliveryCode
from User AS u
INNER JOIN DeliveryTemplate AS d
ON u.ID = d.UserID
end
