SQL join question

Question

I have a table table_a with a column containing data as follows:

.aaa01932
.asd02343
.lkj03342

So it always starts with a period followed by 3 letters, followed by 2 number (01, 02 etc) followed by some extra numbers.

I have another table table_b with data as follows:

aaa01
asd02
lkj03

So it maps to the data in table_a but with the period and the extra numbers at the end omitted.

How do I select from table_a joining on table_b where the data in the shown columns are "equal". By equal I mean that table_a.the_column like '%table_b.the_column%'. So something like:

Select * from table_a 
  join table_b on (table_a.the_column like '%table_b.the_column%');

Except I don't know how to format that like clause to accept a column inside. Maybe I need to concatenate it in somehow?

By using the '%' in the begining , you won't be able to use any index on this field, so not very efficient to join on this column. — guigui42
– guigui42, Commented Sep 23, 2010 at 13:59

Tony Andrews · Accepted Answer · 2010-09-23 14:16:22Z

10

The syntax for the LIKE would be:

table_a.the_column like '%' || table_b.the_column || '%'

An alternative would be SUBSTR:

table_b.the_column = substr(table_a.the_column,2,5)

As some comments have said, the SUBSTR is a better method for 2 reasons:

It can be indexed:

create index on table_a (substr(the_column,2,5));
It is more accurate: you want to match only in that position, not anywhere in the column

edited Sep 23, 2010 at 14:16

answered Sep 23, 2010 at 13:56

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

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

2 Comments

guigui42 Over a year ago

substr will be more suitable in this case (fixed lenght), we can use index at least.

onedaywhen Over a year ago

SUBSTR would be better, assuming the pattern is fixed.

Denis Valeev · Accepted Answer · 2010-09-23 13:56:55Z

2

Try this:

Select * from table_a 
  join table_b on (table_a.the_column like '%' || table_b.the_column || '%');

answered Sep 23, 2010 at 13:56

Denis Valeev

6,03537 silver badges41 bronze badges

Comments

Micah · Accepted Answer · 2010-09-23 13:58:39Z

1

join table_b on substr(table_a.column,1) = table_b.column

answered Sep 23, 2010 at 13:58

Micah

10.4k13 gold badges70 silver badges98 bronze badges

2 Comments

guigui42 Over a year ago

I believe this is a better option than LIKE, since we can use index in this case. Also to be more precise : substr(table_a.column,2,5)

Denis Valeev Over a year ago

I mean substr('.aaa01932',1) would produce 'aaa01932' and then we'll try to join on this condition 'aaa01932' = 'aaa01'

Collectives™ on Stack Overflow

SQL join question

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related