How to join data from multiple table keeping all distinct values-CodePudding

I have three tables :

article :

idArticle
libArticle

stock :

idArticle
idWarehouse
qtyStock

warehouse :

idWarehouse
libWarehouse

In the table stock, I only have the article available So, for exemple, if the data are :

article :

890001 'Article1'
890002 'Article2'

warehouse :

0001 'Warehouse Est'
0002 'Warehouse West'
0003 'Warehouse South'

stock :

890001 0001 50
890001 0002 30
890002 0003 20

I want to make a request which display the qtyStock for each article, with "0" in qtyStock if there is not in stock, AND a line for each warehouse, like :

890001 0001 50
890001 0002 30
890001 0003 0
890002 0001 0
890002 0002 0
890002 0003 20

I tried :

select a.idwarehouse, a.idarticle, s.qtystock  from 
(
select idwarehouse, idarticle
from article, warehouse
where idarticle IN ('890001', '890002')
) a 
left join stock s on a.idwarehouse = s.idwarehouse and a.idarticle = s.idarticle

It works, but is there a better way to do it without that kind of subrequest ?

CodePudding user response：

The result can be achieved with a simple CROSS JOIN of article and warehouse to get all combinations, then LEFT JOIN stock.

WITH
    article (idarticle, libarticle)
    AS
        (SELECT '890001', 'Article1' FROM DUAL
         UNION ALL
         SELECT '890002', 'Article2' FROM DUAL),
    stock (idarticle, idwarehouse, qtystock)
    AS
        (SELECT '890001', '0001', 50 FROM DUAL
         UNION ALL
         SELECT '890001', '0002', 30 FROM DUAL
         UNION ALL
         SELECT '890002', '0003', 20 FROM DUAL),
    warehouse (idwarehouse, libwarehouse)
    AS
        (SELECT '0001', 'Warehouse Est' FROM DUAL
         UNION ALL
         SELECT '0002', 'Warehouse West' FROM DUAL
         UNION ALL
         SELECT '0003', 'Warehouse South' FROM DUAL)
  SELECT a.idarticle, w.idwarehouse, NVL (s.qtystock, 0) AS stock
    FROM article a
         CROSS JOIN warehouse w
         LEFT JOIN stock s ON (a.idarticle = s.idarticle AND w.idwarehouse = s.idwarehouse)
ORDER BY a.idarticle, w.idwarehouse;



   IDARTICLE    IDWAREHOUSE    STOCK
____________ ______________ ________
890001       0001                 50
890001       0002                 30
890001       0003                  0
890002       0001                  0
890002       0002                  0
890002       0003                 20