CODEWITHSUNDEEP
sql
ajm
ajm

Reputation: 13213

SQL Query to find ALL products by user and division?

I am struggling to come up with the SQL query to find the all the products accessible to user under a particular division.

A division can have many products.

Division table has division_id and division_name.

Product table has product_id, product_name and division_id.

I have another table called UserProducts where I have columns like

user_id, division_id and product_id.

In the UI part while assigning products to user, division is selected first and then based on selected division products are populated.

In addition to the products from products table, the product drop down has one more option called as ALL.

When ALL option from product is selected all products under the division are assigned to the user.

But in the User products I do not enter entry for all the products. It has only one entry like 

user_id = 1, division_id = 1 and product_id = -1 
where -1 says its ALL products under division_id = 1

Also user can have access to many divisions. So User can have access to all products under division A but only two products under divison B.

Now, with this schema how do I get all the products accessible to user under division?

EDIT 1

I will have user id and product id as inputs and i would want to get all the products accessible to given user under given division.

Input : user_id, division_id output : List of all products by user_id and division_id

Please help.

Upvotes: 0

Views: 644

Answers (4)

Andriy M
Andriy M

Reputation: 77707

SELECT DISTINCT
  p.product_id,
  p.whatever
FROM UserProducts up
INNER JOIN Product p ON up.product_id IN (-1, p.product_id)
WHERE up.user_id = @user_id
  AND up.division_id = @division_id

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57053

SELECT product_id, product_name and division_id
  FROM Products AS P
 WHERE EXISTS (
               SELECT * 
                 FROM UserProducts AS U
                WHERE U.division_id = P.division_id
                      AND U.product_id = -1
              )
UNION
SELECT product_id, product_name and division_id
  FROM Products AS P
 WHERE EXISTS (
               SELECT * 
                 FROM UserProducts AS U
                WHERE U.division_id = P.division_id
                      AND U.product_id = P.product_id
              );

Upvotes: 1

Farhan Ahmed
Farhan Ahmed

Reputation: 340

This schema is not recommended as it would propose complexities later. For example if a user has all the products in 1 division but then one of the products is revoked, you will have to remove -1 entry and replace with the remaining assigned products.

However staying with this schema you will need to fetch the products via a Stored Procedure which checks if the value is -1 then pass all the products otherwise just the products that are assigned.

Create PROCEDURE [dbo].[sp_UserProductsbyDivision]
    @Division as nvarchar(50),
    @User as int output
AS
BEGIN

    SET @AllProductsFlag=(select min(product_id) from  UserProducts where user_id=@user and division_ID=@Division)

    if @AllProductsFlag=-1
    Begin   
--Select ALL products in that divsion
Select @User, Products.Product_ID from Products where division_id=@division 
    End
    Else
    Begin
Select UserProducts.User_ID, Product_ID from UserProducts where User_ID=@User and Division_ID = @Division
    End           
END

Sorry I haven't been able to create the actual schema and test this but I think this should give you an idea. Debug as necessary!

Upvotes: 0

mleonard87
mleonard87

Reputation: 324

This should do it:

SELECT *
FROM user_products up
JOIN products p ON up.division_id = p.division_id AND (up.product_id = p.product_id OR up.product_id = -1)
WHERE up.user_id = ?????
AND up.division_id = ????

We join from the user_products table onto the products table where either both the division_id and product_id matches, or the division_id matches and the product_id is -1 (thus returning all corresponding products for that division). And finally at the end we restrict to a particular user_id and division id. Simply change the last two lines to change your inputs for example if you wanted to restrict by product instead of division swap: up.division_id = ???? for up.product_id = ????

Upvotes: 0

Related Questions