• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

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
0
stroudtx
Asked:
stroudtx
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
x is the query alias, not a field's alias.
you want to check this for each field individually...

so, this would be what you are looking for:
select *
 from (
  select Catalog2_URL URL from products
 UNION ALL
  select Catalog3_URL from products
  UNION ALL
  select Catalog4_URL from products
   UNION ALL 
   select Catalog5_URL from products
  UNION ALL
   select Catalog6_URL from products
  )  x 
WHERE Instr(URL,'ProductImagesLarge/') > 0

Open in new window


however, a more (db-) efficient query would be:
select Catalog2_URL URL from products where  Instr(Catalog2_URL ,'ProductImagesLarge/') > 0
UNION ALL
select Catalog3_URL URL from products where  Instr(Catalog3_URL ,'ProductImagesLarge/') > 0
UNION ALL
etc etc

Open in new window

0
 
nemws1Commented:
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

0
 
nemws1Commented:
Agh!  angelIII beat me to it. ;-)
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now