skip to Main Content

I have a seller_commissions table, where are related with two other tables: products and sellers (users)

I need to make a painel, where admin can update seller commissions for each product.

Products will be created over time, so I don’t want to insert data in seller_commissions table when this occurs, because I would need to do this multiples times. So, my solution was:
get all products data for user’s update. If seller_commissions are null for specific product, this means the target seller never has your commission updated. In other words, all sellers have commission = 0 in first moment.

I try the following queries:

-- This is the result what I want, but filtering by seller_id, but, unfortannaly this return all products for each seller (I want to specify the seller_id)
select fpp.name as product_name, 
       fsc.seller_id,
       fsc.commission 
from fp_products as fpp 
left join fp_sellers_commissions as fsc 
   on fsc.product_id = fpp.id 
left join fp_users as fpu
   on fpu.id = fsc.seller_id;
   
-- If I use 'where' clause, not all products are returned, because seller_id is none
select fpp.name as product_name, 
       fsc.seller_id,
       fsc.commission 
from fp_products as fpp 
left join fp_sellers_commissions as fsc 
   on fsc.product_id = fpp.id 
left join fp_users as fpu
   on fpu.id = fsc.seller_id
where seller_id = 1;
  • result for first query:
    enter image description here

  • result for second query:
    enter image description here

  • expected results:

product_name seller_id commission
shirt 1 250
shoes null 0
black shirt null 0

In first query, is something similiar with what I want. Get all products and seller_commission, but I want this for a specific seller, but when I try to use WHERE clause, I don’t get all products, because seller_id can be null. I try some variations of these queries, but can’t get the expected result :/. Appreciate any help.

to build the schema, use:

-- Create schema
CREATE TABLE `fp_sellers_commissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `commission` float NOT NULL DEFAULT '0',
  `product_id` int(11) NOT NULL,
  `seller_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `fp_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) CHARACTER SET latin1 NOT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `disabled` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`)
);

CREATE TABLE `fp_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET latin1 NOT NULL,
  `surname` varchar(32) CHARACTER SET latin1 NOT NULL,
   PRIMARY KEY (`id`)
);

-- Inserting data: 
INSERT INTO `fp_products`
(`id`, `name`, `createdAt`, `disabled`)
VALUES
(1, 'shirt', '00:00:00', 0),
(2, 'shoes', '00:00:00', 0),
(3, 'black shirt', '00:00:00', 0);

INSERT INTO `fp_users`
(`id`,
`name`,
`surname`)
VALUES
(1, 'bilbo', 'aaa'),
(2, 'frodo', 'aaa');

INSERT INTO `fp_sellers_commissions`
(`id`, `commission`, `product_id`, `seller_id`)
VALUES
(1, 100, 1, 1),
(2, 500, 1, 2);

Or you can acess SQL FIDDLE: http://sqlfiddle.com/#!9/d6559f/5

2

Answers


  1. I’m not sure why the expected result should be with a commission of "250" for the seller "1", but I think I got what you are searching for. If you want to filter the seller’s commission and still display the other products with nulls, you could put the filter condition directly on the left join, kinda like the following.

    select fpp.name as product_name, 
       fsc.seller_id,
       fsc.commission
    from fp_products as fpp 
    left join fp_sellers_commissions as fsc 
     on fsc.product_id = fpp.id and fsc.seller_id = 1
    left join fp_users as fpu
     on fpu.id = fsc.seller_id;
    

    What happens here, is that the filtering condition is applied at the moment you do the left join, so if it does not match, since it is a "left" join, the results will still be returned with nulls. If you put it in the "where" clause, it will be applied after the join is applied, and it will filter out the results that do not match.

    Login or Signup to reply.
  2. My suggestion is

    select fpp.name as product_name, 
           fsc.seller_id,
           SUM(ifnull(fsc.commission, 0)) as commission
    from fp_products as fpp
    left join fp_sellers_commissions as fsc
    on fpp.id = fsc.product_id and fsc.seller_id = 1
    group by fpp.name, fsc.seller_id
    order by fsc.seller_id desc;
    

    with this must be getting the result you need. Note: I added a group summing to commissions, but if not is the goal, just remove the group by and the sum function.

    Hoping this can help you.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search