Product Table - Nop DB

I need to figure out what the relation is between the product table and the store table (if there is one). I need to write a sql script to pull some info based on store id (ie. SELECT * FROM PRODUCTS WHERE storeID = '11'). Is there a way to do a join that I'm just not seeing?
ANY help is GREATLY appreciated.

Cheers,

Brad

There is another table that links the relationship
Have a look at StoreMapping Table

Id  EntityId  EntityName    StoreId
1  17         Product                          2
5  24         MessageTemplate        2
6  38         Product                          1
2  44         MessageTemplate        1
4  47         MessageTemplate        2

Thank you very much for this info, I'll see what I can work out and update shortly!

Cheers!

Brad

To add on to the point, StoreMapping is a generic table used by several entities. That's why you see the entity name as one of the fields. :)

SAMPLE FOR THIS QUERY IS

      

SELECT * FROM product p WHERE  (p.LimitedToStores = 0 OR EXISTS (
      SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
      WHERE [sm].EntityId = p.Id AND [sm].EntityName = 'Product' and [sm].StoreId=' 1 '))

Sangeet,

Thank you so very much, this helped tremendously. The issue I'm having now, is that our previous developer added a new table called 'TierPrice' (which actually has the correct pricing information per product). So I'm not getting the correct pricing without bringing in this table as well. Admittedly I'm not a db guy lol, Here is what the 'TierPrice' table looks like:

TierPrice: ( https://imgur.com/YXHbtkN -- Image isn't showing below)


Is there a way I can pull the 'Price' from 'TierPrice' Into your query?
(ie: SELECT * FROM product p WHERE  (p.LimitedToStores = 0 OR EXISTS (
      SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
      WHERE [sm].EntityId = p.Id AND [sm].EntityName = 'Product' and [sm].StoreId='11'))   )

Any help is GREATLY appreciated.

Cheers,

Brad

b.aytes wrote:


try with below query to get price into last column of result query

DECLARE @CurrentDate as DATETIME =SYSDATETIME()
DECLARE @CustomerRoleId int
SET @CustomerRoleId=1 -- this is your current login customer role id

SELECT * FROM product p
left  join TierPrice t on p.Id = t.ProductId
and ((T.StartDateTimeUtc <= @CurrentDate OR T.StartDateTimeUtc IS NULL)  AND (@CurrentDate  <= T.EndDateTimeUtc  OR T.EndDateTimeUtc IS NULL))
AND (t.CustomerRoleId =0 or t.CustomerRoleId = @CustomerRoleId OR t.CustomerRoleId is NULL )