asked on

Sub Select Question for MySql

I am trying to get this to work. I need to only return matches fields only containing ProductImagesLarge/

I tried to create it, but I don't know if this is the right strategy and not sure what to do with the where clause.

select * from (select products.Catalog2_URL, products.Catalog3_URL, products.Catalog4_URL, products.Catalog5_URL, products.Catalog6_URL
FROM products) x WHERE Instr(x,'ProductImagesLarge/') > 0

Thanks,

Mike

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nem Schlecht

Okay, well, this isn't quite what you want. You want to search for "ProductImagesLarge/" in multiple different fields in a table. The sub-select you have here won't do what you want - it'll still return a multi-field table - not concat all the fields into a single-field (virtual) table. You can do this, albeit a bit complexly, with UNION (assuming all the _URL fields are defined the same):

(SELECT Catalog2_URL AS URL from products WHERE INSTR(Catalog2_URL, 'ProductImagesLarge/') > 0)
UNION
(SELECT Catalog3_URL AS URL from products WHERE INSTR(Catalog3_URL, 'ProductImagesLarge/') > 0)
UNION
(SELECT Catalog4_URL AS URL from products WHERE INSTR(Catalog4_URL, 'ProductImagesLarge/') > 0)
UNION
(SELECT Catalog5_URL AS URL from products WHERE INSTR(Catalog5_URL, 'ProductImagesLarge/') > 0)
UNION
(SELECT Catalog6_URL AS URL from products WHERE INSTR(Catalog6_URL, 'ProductImagesLarge/') > 0)
;

Open in new window

Nem Schlecht

Agh! angelIII beat me to it. ;-)