troubleshooting Question

How can i combine an IN and a LIKE statement in mysql query?

Avatar of Nemetona
Nemetona asked on
MySQL ServerSQL
7 Comments1 Solution94 ViewsLast Modified:
MySQL 5.5.54

I have two tables, both with the same structure.  I want to select records from table 1 based on the values in table 2.
I have a field, thevalue, which contains values such as:
181-1-0001
181-1-0002
181-2-0001
181-2-0004

I have the query:
SELECT * FROM table1 where thevalue  in(SELECT distinct concat('Like ''',left(rangeid,8),'%''') FROM table2)

when I run this query I don't get an error but 0 rows are returned.  when I run the inner select statement separately it returns

'Like \'181-1-00%\''
'Like \'182-1-00%\''
'Like \'183-2-00%\''
'Like \'187-2-00%\''
'Like \'188-2-00%\''

Can anyone suggest how I can return the rows in table1 where the first 8 characters of the thevalue field match those in the thevalue field of table2?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros