从左表返回所有数据,即使没有陪同的where子句。

huangapple go评论52阅读模式
英文:

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;

  • result for the first query:
    从左表返回所有数据,即使没有陪同的where子句。

  • result for the second query:
    从左表返回所有数据,即使没有陪同的where子句。

  • expected results:

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;
  • result for first query:
    从左表返回所有数据,即使没有陪同的where子句。

  • result for second query:
    从左表返回所有数据,即使没有陪同的where子句。

  • 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

答案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 bysum函数。

希望这能帮助你。

英文:

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.

huangapple
  • 本文由 发表于 2023年2月14日 02:22:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/75439830.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定