SQL retrieve results based on String matching from two tables

Question

I have two tables say A and B. A has many columns like Date, Customer, Price, typedesc etc. B has only one column typedesc. I want to retrieve rows in A whose typedesc is in B. So I wrote

select * from A where typedesc in (select typedesc from B)

I got 0 rows in result. So i tried

select A.* from A inner join B on A.typedesc=B.typedesc

Still I am getting 0 rows in result

I manually checked the typedesc column in both tables, there are matching entries. typedesc contains strings and it is of type varchar2

Here are the sample tables

A

DATE       CUSTOMER      TYPEDESC      SKU        PRICE
02/01/2013    4567     CREAM CORDIALS   1234       23
03/01/2013    3256     U.S. BRANDY      3322       10.5

B

TYPEDESC

CREAM CORDIALS
FIRE WHISKY

your queries should work just fine. can you give sample records? — John Woo
– John Woo, Commented Jun 4, 2013 at 0:45
your query should work: sqlfiddle.com/#!4/93a4d/1 please check the records if there are extra spaces. — John Woo
– John Woo, Commented Jun 4, 2013 at 0:52

Juned Ahsan · Accepted Answer · 2013-06-04 00:57:24Z

1

Try to use the TRIM function before comparison to avoid the mismatch due to extra spaces.

answered Jun 4, 2013 at 0:57

Juned Ahsan

68.9k12 gold badges101 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2133404 Over a year ago

Thanks, it worked. The spacing seems to be correct when I see in output. But as you said, it needed trim.

John Woo Over a year ago

do not use TRIM() when joining table since it will kill the indexes if you have one. The best way to do is to update the record by trimming it eg. UPDATE B SET TYPEDESC = TRIM(TYPEDESC), so will not end up joining like this A.typedesc=TRIM(B.typedesc) -- bad.

Collectives™ on Stack Overflow

SQL retrieve results based on String matching from two tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related