cancel
Showing results for 
Search instead for 
Did you mean: 

How to query products with image data

Former Member
0 Kudos

I need to retrieve products with media images' url (at least one url). I can not find product and media relation. If i execute below flexible search query, returns no rows because of there is no information(checked with not null) on picture field on product item records.

select {sl.productCode} as stock_prodCode,{prod.name[tr]} as prod_name,{c.name[tr]} as c_name, {c.code}, {cv.version} as cv_version, {sl.available} as available from { StockLevel as sl join Product as prod on ({prod.code}=substring({sl.productCode},1,11) and {sl.available}>1) left join CategoryProductRelation as cpr on {prod.pk}={cpr.target} left join Category as c on {c.pk}={cpr.source} join Catalog as cat on {cat.pk}={c.catalog} and {cat.id}='kotonProductCatalog' join CatalogVersion as cv on ({cv.pk}={cat.activeCatalogVersion} and {cv.version}='Online') join Media as pic on {pic.pk}={prod.picture} } order by {prod.code} Brgds

Accepted Solutions (0)

Answers (1)

Answers (1)

0 Kudos

Hi Mustafa, Include the below where clause in your query "WHERE {pic:INTERNALURL} IS NOT NULL"

Former Member
0 Kudos

Thank you for your reply. Maybe your information makes sense as well. But my complaint was actually on the relation part. I meant to how can i join with prod and media. When i try with prod.picture , it returns no rows because of prod.picture field is empty.