SEO

#1 (**permalink**) 02-27-2008, 06:54 AM

I have
create table table1 (col1 varchar(50));
insert into table1 values ('abc'), ('defg');

create table table2 (col2 varchar(50));
insert into table2 values ('abc d'), ('defg h'), ('abcd'), ('defgh');

And I need those as the result
'abc', 'abc d'
'defg', 'defg h'

select col1, col2 from table1 join table2 on ?

#2 (**permalink**) 02-27-2008, 06:54 AM

select col1, col2 from table1, (select col2, (CASE WHEN posstr(col2,'
') > 0 then substr(col2,1,posstr(col2,' ')) else col2 end) as tempcol
from table2) as temptable where col1 = tempcol

regards

Mehmet Baserdem

#3 (**permalink**) 02-27-2008, 06:54 AM

DECLARE GLOBAL TEMPORARY TABLE Table1 (Col1 VARCHAR(50))
INSERT INTO SESSION.Table1 VALUES ('abc'), ('defg')

DECLARE GLOBAL TEMPORARY TABLE Table2 (Col2 VARCHAR(50))
INSERT INTO SESSION.Table2 values ('abc d'), ('defg h'), ('abcd'),
('defgh')

SELECT Col1, Col2 FROM SESSION.Table1, SESSION.Table2 WHERE
VARCHAR(Col1 || ' ' || REPLACE(Col2, Col1 || ' ', ''), 50) = Col2

DROP TABLE SESSION.Table1
DROP TABLE SESSION.Table2
COMMIT

B.

#4 (**permalink**) 02-27-2008, 06:54 AM

Hmm.. forget this. It fails if col1 shows up twice in col2.

Mehmet's answer is very good.

B.

#5 (**permalink**) 02-27-2008, 06:55 AM

SELECT col1, col2
FROM table1
JOIN
table2
ON col1 = SUBSTR(col2, 1, POSSTR(col2||' ', ' ')-1)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

How to join those 2 char columns?