Left Join with Case statement on String columns

Question

Post reply

Left Join with Case statement on String columns

mystudyref

Valued Member

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

#399815

Hi.
I have two tables like
TABLE 1
ID Info Detail
1 TableAvailble 0
2 Table color blue
3 table detail Wood
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
2 Table color blue
3 table detail Wood

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

John Mitchell-245523

SSC Guru

Points: 148809

March 7, 2016 at 7:54 am

#1863468

SELECT

ID

, Info

, Detail

, '' AS [Key]

FROM Table1

John

mystudyref Valued Member Points: 64 More actions · Answer 2

John, here is my complete question.... ,

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?

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

How many times are you going to post the same question and not respond to the people trying to help?

http://www.sqlservercentral.com/Forums/Topic1767262-391-1.aspx

_______________________________________________________________

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/