Reputation: 95
I have a view in my database that uses several tables and everything works fine the way it is now. I want to add another column/value based on whether a specific match is found in another table. I have used similar iterations in others, but this time I get a NULL result, even though there is a match.
Basically:
Table_2
ID | Value | Text |
---|---|---|
1 | Miss | Some text |
1 | Miss | Other text |
2 | Hit | OK |
2 | Miss | Always |
3 | Hit | Whatelse |
There is a correlation to another table that holds a value, equal to the ID in Table_2.
I want to use a case when and set a text based on this;
select
somefield1,
somefield2,
somefield3,
case
when 2.ID is not null then 'Real'
else 'Fake'
end somefield4
from
table_1 1 (nolock)
left join
(select top (1) ID from table_2 where Value = 'Hit') 2 on 1.ID = 2.ID
If I run this script :
select top (1) ID
from table_2
where Value = 'Hit' and ID = 2
it returns: 2
If I run this :
select top (1) ID
from table_2
where Value = 'Hit' and ID = 1
it returns: NULL
But when I run the view, I get 'Fake' every single time, even though there is a match and the query returns a value.
Upvotes: 1
Views: 80
Reputation: 1269563
If I understand correctly, you can use exists
to check if the appropriate row exists in the other table. This looks like:
select t2.*,
(case when exists (select 1
from table_1 t1
where t1.id = t2.id and t1.event = 'Hit'
)
then 'real' else 'fake'
end)
from table_2 t2;
For performance, you want an index on table_1(id, event)
.
Upvotes: 2