CODEWITHSUNDEEP
mysqlsqlselectjoin
xtyp
xtyp

Reputation: 49

Select merge two tables with one between each

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:

http://i45.tinypic.com/2zzqjco.png

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.

Upvotes: 2

Views: 133

Answers (1)

John Woo
John Woo

Reputation: 263723

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

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

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

RESULT

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

Upvotes: 3

Related Questions