SQL Help please

[Stormpool]

Hi I'm having a bit of problem with my SQL code, I would appreciate some help.
I need to output a product ID, Name, short description etc and also the
images.. the images are held in a seperate table like this I have a table with
basically.. Product ID Image type Image Name
100_____1_________image_Small.jpg 100_____2_________image_medium.jpg
100_____3_________image_large.jpg 101_____1_________image_Small.jpg
101_____2_________image_medium.jpg 101_____3_________image_large.jpg I need
images for each product where the image type is EQ to 2 or 3. Now I can't pull
both filenames into the same field, so I would have to make new fields for the
imagename.. like imagemedium and imagelarge. The following statement does what
I need if I only wanted one picture with the image type of 2. I wrote a
subquery but it brought back more than one line of data.. so I didn't do that
one right! Select distinct tbl_skus.sku_productid, tbl_products.product_name,
tbl_products.product_shortdescription, tbl_prdtimages.prdctimage_filename,
tbl_skus.sku_price, tbl_products.product_stockstatus from (tbl_skus inner join
tbl_products on tbl_skus.sku_productID = tbl_products.product_ID) inner join
tbl_prdtimages on tbl_products.product_ID = tbl_prdtimages.prdctImage_productID
where tbl_products.product_onweb = '1' and tbl_products.product_siteid = '9'
and tbl_prdtimages.prdctimage_imgtypeid = '2' order by
tbl_products.product_name Thanks for help, Dan

[The ScareCrow]

This is going to be dependant upon which db your using, but

and tbl_prdtimages.prdctimage_imgtypeid IN (2,3)

Or using sub query

Select columns
from table T1 Inner Join (Select ProductId, ImageName From ImageTable Where
ImageType = 2) T2 On T1.ProductId = T2.ProductId

Then you can just add another inner join for type 3.

Plus there are a number of other ways, but it depends on the db.

Ken

[Stormpool]

Using an SQL db, I tried the 'and tbl_prdtimages.prdctimage_imgtypeid IN
(2,3)' Just to check it's output, andit gives the product twice where there
are two images, which I didn't want to do. Actually I definately can't do that
because the two types of images are in different locations and when I output
the data to a CSV if they are using the same field heading you won't know what
Location they are in.

[The ScareCrow]

You still did not indicate which db your using. There are a lot of sql db's

Actually I definately can't do that because the two types of images are in
different locations and when I output the data to a CSV if they are using the
same field heading you won't know what Location they are in.

How do you intend to get the location, if it's not in the db ??

Even though there are 2 records returned (which is what should be returned,
because there are 2 images), the recordset can still be output correctly using
cf.

Do some research on the "group" attribute of the cfoutput tag.

Ken