I have two tables. First table has products and second table have sales made by agents. Each agent can make 1 or more sales of a product but not more than "max_sales_allowed_per_agent". I am only interested about current agent, let's call him "John" (agent_id). I would like to get a list of all products where John has not sold max allowed nr of that item yet. I was trying subselect and joins but I can't make it work :-(

What do I need?

I need all the products where:

    agent_id = John (Table SALES)
    status of product is "active" (Table PRODUCTS)
    number of sales John made is less than "max_sales_required_per_agent" 

Table PRODUCTS

    product_id
    status [active|inactive]
    max_sales_required_per_agent

Table SALES each person can make x sales of same product

    agent_id
    date_of_sale
    product_id_sold

Example of data in tables

PRODUCTS:

    Book   active   2
    Pen    active   3
    Laptop inactive 2
    Camera active   11

SALES:

    John  2022-10-01  Book
    John  2022-10-02  Book
    Nancy 2022-09-03  Pen
    Jack  2022-08-11  Book
    John  2022-07-22  Camera
    John  2022-05-01  Camera
    John  2022-03-03  Laptop

Result expected

I need to query to return me all active products for John that didn't reach max_sales_required yet and I need nr of sales John made for product and max_sales_required. I need to create a query to return me this for John:

    Laptop (1 sale out of 2 possible) --> not returning because product is inactive
    Camera (2 sales out of 11 possible)
    Book (2 sales out of 2 possible) --> not returning because 2 sales of 2 max allowed

Please help!

CodePudding user response：

Will the script below be helpful?

SELECT [MainProducts].[product_id], [MainProducts].[status]
FROM [PRODUCTS] AS [MainProducts] JOIN [SALES] ON [MainProducts].[product_id] = [SALES].[product_id_sold]
WHERE [MainProducts].[status] = 'active'
AND [SALES].[agent_id] = 'John'
AND [MainProducts].[max_sales_required_per_agent] <= (SELECT [max_sales_required_per_agent] FROM [PRODUCTS] WHERE [product_id] = [MainProducts].[product_id])

Another way:

SELECT COUNT(*) AS [Counter], [MainProducts].[product_id]
FROM [PRODUCTS] AS [MainProducts] JOIN [SALES] ON [MainProducts].[product_id] = [SALES].[product_id_sold]
WHERE [MainProducts].[status] = 'active'
AND [SALES].[agent_id] = 'John'
AND [MainProducts].[max_sales_required_per_agent] <= (SELECT [max_sales_required_per_agent] FROM [PRODUCTS] WHERE [product_id] = [MainProducts].[product_id])
GROUP BY [MainProducts].[product_id]

CodePudding user response：

Maybe something like:

select p1.product_id,
       p1.max_sales_required_per_agent,
       p1.status,
       tbl.prod_sold
from products p1
inner join ( select s.product_id_sold,count(*) as prod_sold
             from sales s
             inner join products p on p.product_id=s.product_id_sold
             where agent_id='John' and p.status='active'
             group by product_id_sold 
           ) tbl on tbl.product_id_sold=p1.product_id and prod_sold < max_sales_required_per_agent;

Result:

product_id    max_sales_required_per_agent    status   prod_sold
     Camera                11                  active      2

https://dbfiddle.uk/9HuBWS-h

If status have active/inactive values and you only want to select the products where all values are active you should add having count(distinct status) = 1 condition as follows:

select p1.product_id,
       p1.max_sales_required_per_agent,
       p1.status,
       tbl.prod_sold
from products p1
inner join ( select s.product_id_sold,count(*) as prod_sold
             from sales s
             inner join products p on p.product_id=s.product_id_sold
             where agent_id='John' and p.status='active'
             group by product_id_sold 
             having count(distinct status) = 1
           ) tbl on tbl.product_id_sold=p1.product_id and prod_sold < max_sales_required_per_agent;