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
