Left Join with Case statement on String columns

Question

Left Join with Case statement on String columns

mystudyref

Valued Member

Points: 64
More actions
March 7, 2016 at 7:50 am

#325352

Hi.
I have two tables like
TABLE 1
ID Info Detail
1 TableAvailble 0
2 Table color blue
3 table detail Wood
4 IsPicAvailable 1
TABLE 2
DetailKey Detail
1 No
2 Yes
3 Green
4 Blue
5 Orange
6 Wooden
7 Other
I have to check the details from Table1 on Table2 and get the Key ... Result should be like
ID Info Detail Key
1 TableAvailble 0 1
2 Table color blue 4
3 table detail Wood 6
4 IsPicAvailable 1 2
As you can see, the string compare of Wood and Wooden has to result the same Key. So here is the query i did
select a.ID, a.Info, a.Detail, b.Key
from table1 a
left join TABLE2 b
ON
a.detail = case when a.detail = '0' then 'No'
when a.detail = '1' then 'Yes'
end
OR
CHARINDEX(a.detail, b.detail )>0
problem:
a. Its not selecting 1 for yes and 0 for no
it is selecting wood/wooden values properly. what am i doing wrong?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Hi and welcome to SSC. We can't possibly offer much advice here because it is not at all clear what you are trying to do. Please take a few minutes and read this article. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]. Or you can also check out the first link in my signature.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Y.B. SSChampion Points: 11649 More actions · Answer 2

This is not an answer to your question but that doesn't mean I'm not trying to help. Consider the code below. This is an example on how to submit code. I also show you the difference between your table and a normalized table. Having proper tables to begin with will make all the difference in the world and make it easier on you.

DECLARE @table1 TABLE (ID INT, Info VARCHAR(20), Detail VARCHAR(20))

INSERT INTO @table1

VALUES (1, 'TableAvailable', '0'), (2, 'TableColor', 'Blue'), (3, 'Table Detail', 'Wood'), (4, 'IsPicAvailable', '1')

SELECT * FROM @table1

DECLARE @goodtable1 TABLE (ID INT, Available bit, Color VARCHAR(20), Detail VARCHAR(20), IsPicAvailable bit)

INSERT INTO @goodtable1

VALUES (1, 0, 'blue', 'Wood', 1)

SELECT * FROM @goodtable1

SELECT quote FROM brain WHERE original = 1
0 rows returned