英文:
Return all data from left table, even if where clause is not attended
问题
以下是您要翻译的内容:
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 the first moment.
I try the following queries:
-- This is the result what I want, but filtering by seller_id, but, unfortunately this returns 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 the first query, something is similar to what I want. Get all products and seller_commission, but I want this for a specific seller, but when I try to use the WHERE clause, I don't get all products because seller_id can be null. I tried 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 access SQL FIDDLE: http://sqlfiddle.com/#!9/d6559f/5
英文:
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
答案1
得分: 1
我不确定为什么预期结果应该在卖家"1"的佣金中带有"250",但我认为我明白你在寻找什么。如果你想过滤卖家的佣金并仍然显示其他带有空值的产品,你可以直接将过滤条件放在左连接上,就像下面这样。
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;
这里发生的是,在执行左连接时应用了过滤条件,因此如果不匹配,由于它是一个"left"连接,结果仍将返回为null。如果将它放在"where"子句中,它将在连接应用之后应用,并过滤掉不匹配的结果。
英文:
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.
答案2
得分: 1
我的建议是:
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;
通过这个查询,你应该能够获得所需的结果。注意:我添加了对佣金的分组求和,但如果不是目标,请删除group by
和sum
函数。
希望这能帮助你。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论