SQL query from "many to many" table excluding some data with sorting

Question

Good evening, developers!

Few days I'm on one query - maybe you can help me. I take two tables from my database for example:

PRODUCTS

| id | name | type|
| 1  | product-1  | base  |
| 2  | product-1a | modif |
| 3  | product-1b | modif |
| 4  | product-2  | base  |
| 5  | product-2a | modif |

PRODUCT_SOURCE

| id  | product_id | store_id | quantity | 
|  1  |      2     |     1    |     0    | 
|  2  |      2     |     2    |     5    | 
|  3  |      2     |     3    |     3    | 
|  4  |      3     |     1    |     7    | 
|  5  |      3     |     3    |     0    | 
|  6  |      5     |     3    |     1    | 

where in the table PRODUCT_SOURCE goes only products with type "modif". So I need to get all the products from table PRODUCTS with the quantity form PRODUCT_SOURCE WITHOUT showing the quantities from other stores (without duplicates) BUT with possibility to sort like shown below:

| id |    name    | type  | quantity |
| 3  | product-1b | modif |     7    |
| 2  | product-1a | modif |     0    |
| 6  | product-2a | modif |     0    |
| 1  | product-1  | base  |     -    |
| 4  | product-2  | base  |     -    |

I have tried INNER / OUTER JOINs, GROUP BY even tried to put SELECT FROM in ORDER BY clause (I believed it is possible...)

P.S. I have nested query. Something like:

SELECT * FROM (SELECT sp.id, sp.name, sp.type, vat.amount, source.shop_id, source.quantity, sp.created_at FROM public."product" sp FULL OUTER JOIN public."product_source" as source on sp.id = source.product_id INNER JOIN public."vat" as vat on sp.vat_id = vat.id WHERE sp.account_id = 1 AND source.shop_id = 1)AS filtered ORDER BY filtered.quantity desc

Thank you! I would appreciate any help.

Answer 1

You can use a correlated subquery:

select p.*,
       (select ps.quantity
        from product_source ps
        where ps.product_id = p.id and ps.store_id = 1
       ) as quantity
from products p;

If you want this for all stores you can use a cross join in the from clause:

select p.*,
       (select ps.quantity
        from product_source ps
        where ps.product_id = p.id and ps.store_id = s.id
       ) as quantity
from products p cross join
     stores s;

Answer 2

Schema (MySQL v5.7)

create table PRODUCTS  (id int , name varchar(50) , type varchar(50));
insert into PRODUCTS values(1 , 'product-1' , 'base');
insert into PRODUCTS values(2 , 'product-1a' , 'modif');
insert into PRODUCTS values(3 , 'product-1b' , 'modif');
insert into PRODUCTS values(4 , 'product-2' , 'base');
                       
create table PRODUCT_SOURCE( id  int, product_id int , store_id int , quantity int);
insert into PRODUCT_SOURCE values(  1  ,      2     ,     1    ,     0    );
insert into PRODUCT_SOURCE values(  2  ,      2     ,     2    ,     5    );
insert into PRODUCT_SOURCE values(  3  ,      2     ,     3    ,     3    );
insert into PRODUCT_SOURCE values(  4  ,      3     ,     1    ,     7    );
insert into PRODUCT_SOURCE values(  5  ,      3     ,     3    ,     0    );

---

Query #1

select p.id,p.name,p.type,ps.quantity from 
PRODUCTS p left join  PRODUCT_SOURCE ps on p.id=ps.product_id and store_id=1
order by p.id;

id	name	type	quantity
1	product-1	base
2	product-1a	modif	0
3	product-1b	modif	7
4	product-2	base

---

View on DB Fiddle

If there is store wise product wise multiple row in PRODUCT_SOURCE table then to have sum(quantity) you can use below query:

Schema (MySQL v5.7)

create table PRODUCTS  (id int , name varchar(50) , type varchar(50));
insert into PRODUCTS values(1 , 'product-1' , 'base');
insert into PRODUCTS values(2 , 'product-1a' , 'modif');
insert into PRODUCTS values(3 , 'product-1b' , 'modif');
insert into PRODUCTS values(4 , 'product-2' , 'base');
                       
create table PRODUCT_SOURCE( id  int, product_id int , store_id int , quantity int);
insert into PRODUCT_SOURCE values(  1  ,      2     ,     1    ,     0    );
insert into PRODUCT_SOURCE values(  2  ,      2     ,     2    ,     5    );
insert into PRODUCT_SOURCE values(  3  ,      2     ,     3    ,     3    );
insert into PRODUCT_SOURCE values(  4  ,      3     ,     1    ,     7    );
insert into PRODUCT_SOURCE values(  5  ,      3     ,     3    ,     0    );

---

Query #1

select p.id,p.name,p.type,ps.quantity from 
PRODUCTS p left join 
(select product_id,sum(quantity) quantity 
from PRODUCT_SOURCE ps where store_id=1
group by product_id) ps
on p.id=ps.product_id;

id	name	type	quantity
1	product-1	base
2	product-1a	modif	0
3	product-1b	modif	7
4	product-2	base

---

View on DB Fiddle