Custom SQL query help

[Simon Knott]

Hi All

I am trying to write a custom query that will generate me a report which shows me the following columns in ustore:

ProductID, CatalogNo,Product Name, Product Profile Name

Now I am able to get the first 3 by using this simple query below:

SELECT P.ProductID,P.CatalogNo AS SKU,PC.Name AS NAME
  FROM [uStore].[dbo].[Product] P
  JOIN Product_Culture PC on P.ProductID = PC.ProductID
 Where P.StoreID = 000 and PC.CultureID = 8

However the product profile name is the part I am struggling to get. I worked out it looks like a product profile name is stored as another product and has the flag of isProfile = 1. But I cant see how I can tie the two together?

Any help is greatly appreciated.

Thanks

[west-digital.fr]

Hello

Would you like to try this query?

DECLARE @CultureID int = 8
DECLARE @StoreID int = 000

SELECT
    P.ProductID
    , COALESCE(
        (SELECT PC.Name
         FROM Product_Culture PC
         WHERE PC.ProductID = P.ProductID AND PC.CultureID = @CultureID),
        '[no localization]'
    ) AS ProductName
    , COALESCE(
        (SELECT PC_Profile.Name
         FROM ProductProfileDependency PPD
         JOIN Product_Culture PC_Profile ON PPD.ProfileID = PC_Profile.ProductID AND PC_Profile.CultureID = @CultureID
         WHERE PPD.ProductID = P.ProductID AND PPD.ObjectTypeID = 1),
        ''
    ) AS ProfileName
FROM
    Product P
WHERE
    P.IsProfile = 0 AND P.StoreID = @StoreID

I did not pay attention at performance, though.

west-digital.fr

[Rebecca Brooks]

Hi Simon, 

I was able to get your query to work by changing Where P.StoreID IS NULL: 

SELECT P.ProductID, P.CatalogNo AS SKU, PC.Name AS NAME

  FROM [uStore].[dbo].[Product] P
  JOIN Product_Culture PC on P.ProductID = PC.ProductID

 Where P.StoreID IS NULL and PC.CultureID = 8

Hope that helps! 

[Simon Knott]

Hi 

That helped and did the trick thanks very much!