List:General Discussion« Previous MessageNext Message »
From:Jeff Drew Date:September 25 2006 2:27pm
Subject:subquery performance
View as plain text  
I have a query with a subquery which does not throw an error, but does not
return either.   I've been testing the query using mysql Query Browser and
the poor dolphin jumps only once a minute or so ;)   I use MYSQL's excellent
error documentation heavily so if the query doesn't throw an error, I'm at a
disadvantage.  Is there an optimization or just better syntax to use?

THE DATA
I have a table that contains an abbreviated identifier and a full
identifier.  The real data is a bit messy so here's a sanitized example:

Abbreviated Column contents:   TR123, RG456
Full Identifier Column contents: TR 123 abc, RG 456 def

THE QUERY
My intent is to:
1. select some of the Abbreviated Column and convert that to a selection for
the Full Identifier Column by:
        - extracting the first 2 characters
        - inserting a space
        - selecting the last 3 characters
        - appending "%" so I can match any of the last 3 characters in the
Full Identifier
2. select rows from Full Identifier Column based on #1

PROBLEMS
I think I have two problems:
1. "in... %" syntax  is not present in any examples I've seen.  They are all
"like... %"  so "in" may not work.
2. Here's query that runs, but does not return:

select name, address from testTable where FullIdentifier in ( select concat
( substring ( AbbreviatedIdentifier,1,2) , " " ,
substring(AbbreviatedIdentifier from 3) , "%" ) from testTable where name
like 'Daisy'));

My left join attempt complained  because the data is all in one table. Is
there a better solution than my  FullIdentifier in(select...  ?

I am not an SQL expert so I'd appreciate any ideas on how to correct this
query.

Thanks

Thread
subquery performanceJeff Drew25 Sep
  • Re: subquery performanceMichael Stassen25 Sep
    • Re: subquery performanceJeff Drew25 Sep