Query problem

Hello all Informixers I have a small problem which I am sure that people of your experience and planet sized brains will be able to sort out in a trice. My SQL is a bit rusty, which is the main reason for the posting. I have 2 tables Table A Table B Col1 Col2 Col3 Col1 Col2 Col3 I want to be able to return all rows in Table B that match whatever is in TableA.Col3. Heres the interesting bit. TablA.Col3 can be either the whole value or the first part of the value in TableB.Col1, Col2 or Col3. i.e. if TableA.Col3 = "507A" it should return all rows in TableB if it contains "507A-7768" in any of the 3 columns. I will appreciate any assistance. Cheers Sean

Vlad the Impailer wrote: Impaler is right! This is a sticky wicket, but here goes: > Hello all Informixers > > I have a small problem which I am sure that people of your experience and > planet sized brains will be able to sort out in a trice. > > My SQL is a bit rusty, which is the main reason for the posting. > > I have 2 tables > > Table A Table B > Col1 Col2 Col3 Col1 Col2 Col3 > > I want to be able to return all rows in Table B that match whatever is in > TableA.Col3. > > Heres the interesting bit. TablA.Col3 can be either the whole value or the > first part of the value in TableB.Col1, Col2 or Col3. > > i.e. if TableA.Col3 = "507A" it should return all rows in TableB if it > contains "507A-7768" in any of the 3 columns. SELECT b.* FROM TableA a, TableB b WHERE a.col3 = substr( b.col1, length(a.col3 ) UNION SELECT b.* FROM TableA a, TableB b WHERE a.col3 = substr( b.col2, length(a.col3 ) UNION SELECT b.* FROM TableA a, TableB b WHERE a.col3 = substr( b.col3, length(a.col3 ) ; Another way, not neccessarily faster so test both: SELECT b.* FROM TableA a, TableB b WHERE a.col3 = substr( b.col1, length(a.col3 ) OR a.col3 = substr( b.col2, length(a.col3 ) OR a.col3 = substr( b.col3, length(a.col3 ); According to Kagel's First Law of SQL there must be a third way which may yet be the best, but I'll leave that one for you (or anyone else out there who is brave). Art S. Kagel

Vlad the Impailer wrote: > Hello all Informixers > > I have a small problem which I am sure that people of your experience and > planet sized brains will be able to sort out in a trice. > > My SQL is a bit rusty, which is the main reason for the posting. > > I have 2 tables > > Table A Table B > Col1 Col2 Col3 Col1 Col2 Col3 > > I want to be able to return all rows in Table B that match whatever is in > TableA.Col3. > > Heres the interesting bit. TablA.Col3 can be either the whole value or the > first part of the value in TableB.Col1, Col2 or Col3. > > i.e. if TableA.Col3 = "507A" it should return all rows in TableB if it > contains "507A-7768" in any of the 3 columns. > > I will appreciate any assistance. > > Cheers > > Sean SELECT * FROM tablA A, tablB B WHERE A.col3 LIKE '%' || B.col1 || '%' OR A.col3 LIKE '%' || B.col2 || '%' OR A.col3 LIKE '%' || B.col3 || '%';Note: Query won't use any Index. bye, michael