You’re offline. This is a read only version of the page.
335
HI, Below is the sql query to get the related products of an item.
it can be helpful for many because sharing is caring.
-------------------------------------- sql query to get the related products of an item -------------------------------------
Select Inv.ITEMID, ET.NAME ,cat.NAME as Category,RlT.NAME as 'Relation Type'
,(select Itemid from inventtable where inventtable.PRODUCT = pp.RECID and inventtable.DATAAREAID='ABC' ) as 'Related Product'
from INVENTTABLE inv
inner join ECORESPRODUCTTRANSLATION as ET on Inv.PRODUCT = ET.PRODUCT AND ET.LANGUAGEID = 'en-us'
left outer join ECORESPRODUCT p on p.RECID = inv.PRODUCT
left outer join ECORESPRODUCTCATEGORY PC on pc.PRODUCT = p.RECID
left outer join ECORESCATEGORY cat on cat.RECID = pc.CATEGORY
inner join ECORESPRODUCTRELATIONTABLE as RT on RT.PRODUCT1 = p.RECID
inner join ECORESPRODUCT pp on pp.RECID = RT.PRODUCT2
inner join ECORESPRODUCTRELATIONTYPE RlT on RlT.RECID = RT.PRODUCTRELATIONTYPE
--inner join INVENTTABLE PPINV on PPINV.PRODUCT = pp.RECID
where inv.DATAAREAID='ABC'
ORDER BY Inv.ITEMID DESC
---------------------------------------------------------------------------------------------------------------------------------------------
Rgards
zeeshan adeel
Hi Zeeshan, thank you for sharing your knowledge, but note that discussion forums are intended for discussion. A better place for this kind of posts would be a blog. You can have one even here at the Dynamics Community site - look at Info Center > Request a New Blog.
Also, when posting code in the forum, please use Insert > Insert Code (in the rich formatting view). For example:
select inv.ITEMID, ET.NAME, cat.NAME as Category, RlT.NAME as 'Relation Type', (select ItemId from InventTable where InventTable.PRODUCT = pp.RECID and InventTable.DATAAREAID = 'ABC' ) as 'Related Product' from INVENTTABLE inv inner join ECORESPRODUCTTRANSLATION as ET on inv.PRODUCT = ET.PRODUCT AND ET.LANGUAGEID = 'en-us' left outer join ECORESPRODUCT p on p.RECID = inv.PRODUCT left outer join ECORESPRODUCTCATEGORY PC on pc.PRODUCT = p.RECID left outer join ECORESCATEGORY cat on cat.RECID = pc.CATEGORY inner join ECORESPRODUCTRELATIONTABLE as RT on RT.PRODUCT1 = p.RECID inner join ECORESPRODUCT pp on pp.RECID = RT.PRODUCT2 inner join ECORESPRODUCTRELATIONTYPE RlT on RlT.RECID = RT.PRODUCTRELATIONTYPE where inv.DATAAREAID = 'ABC' ORDER BY inv.ITEMID DESC
Share
Report
All responses (
Answers (
