选择合并两个表，每个表之间有一个

I've been looking how to achive this, but I don't even know how it's called (for searching it). So here what I have:

我一直在寻找如何实现这一点,但我甚至不知道它是如何调用的(用于搜索它)。所以我在这里:

The tables data are like this:

表数据如下:

[Products]
ID -  Name
 1 - Apple
 2 - Banana

[Storehouses]
ID  -    Name
 1  -  General
 2  -   Other

[Stocks]
Product - Storehouse - Stock
    1   -     1      -  4
    1   -     2      -  4
    2   -     1      -  5

Here I want get all the products on 'Storehouse' = '2' 
but if not exists return 'null' or '0' And what I pretend to get is:

[SELECT]
Product  -  Stock
   1     -    4
   2     -    null OR '0'

I don't know wich statement use, so at least I need a clue. Thanks.

我不知道哪个声明使用,所以至少我需要一个线索。谢谢。

1 个解决方案

#1

SELECT  a.ID,
        a.Name,
        COALESCE(b.Stock, 0) Stock
FROM    Products a
        LEFT JOIN   stocks b
            ON  a.ID = b.Product AND
                b.StoreHouse = 2

• SQLFiddle Demo

To further gain more knowledge about joins, kindly visit the link below:

要进一步了解联接,请访问以下链接:

• Visual Representation of SQL Joins

SQL连接的可视化表示

Lastly, store quantity of the product as INT, not VARCHAR.

最后,将产品的数量存储为INT,而不是VARCHAR。

RESULT

╔════╦════════╦═══════╗
║ ID ║  NAME  ║ STOCK ║
╠════╬════════╬═══════╣
║  1 ║ Apple  ║     4 ║
║  2 ║ Banana ║     0 ║
╚════╩════════╩═══════╝