Select...Where tb1.field in tb2.field

[webmigit]

I have a query that I'm trying to run...

InvImages has two fields ImageFile and AutoID.

Products has Name, CatID and Images. The current design is that products can have multiple images.. so in my query I wanted to run this query..

Images is filled with Image IDs..

One product's Images field may say 12 while another may say 34,35.

  select name,images,imagename,imagefile,autoid,new
  from products,inventoryimages
  where catID = #url.parent#
  and convert(nvarchar(50),autoID) in (images)

And it returns every row where one id is listed, but does not return rows where the Images field has two IDs or more..

Thanks for the help

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[fredericofonseca]

if you have two ID's within the same field, then that is bad design, as each image should be on its own record.


Only option, if the ID within the IMAGES field are always the same length, is to use substrings.

e.g.
(convert(nvarchar(50),autoID) = substring(images,1,10)
or
convert(nvarchar(50),autoID) = substring(images,11,10))

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[webmigit]

Actually, I have found an option using a udf that parses the two rows into a third table.. The UDF is fired by a trigger. I like it, it works pretty seamlessly.

Thanks

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[r937]

udf? third table? trigger? rube goldberg!!! ;-)

select p.name
     , p.images
     , i.imagename
     , i.imagefile
     , i.autoid
     , i.new
  from products as p
inner
  join inventoryimages
    on ',' + p.images + ','
 like '%,' + i.autoid + ',%'
 where p.catID = #url.parent#

this works well, but it won't scale (requires table scan)

redesign with normalized tables is strongly recommended



r937.com | rudy.ca