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;
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
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.
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.
My suggestion is
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 thesum
function.Hoping this can help you.