php - Get undefined join values

617

I have 3 tables: a shop table, a stock table, and a stock_amounts table. The shop table defines each shop's details, the stock tables defines all stock items, and the stock_amounts table defines how much stock each shop has of an item.

My tables look like this:

*Shop table:
| id | shop

I want to display all the stock that a shop has, but also display the stock that hasn't been defined for that shop. For example, shop 1 has 5 of stock 1, but none of stock 2 or stock 3. I want stock 2 and 3 to display as either zero or NULL value.

I'm currently joining it all together. If I do not specify anything in the{-code-2} clause, all shops with their stock is shown, and NULL is shown for undefined values. If I do{-code-2} stock_amounts.id <> '2', then all NULL values no longer show.

What I want to know:
1) Why does NULL no longer show if I specify 'not 2' in the {-code-2} clause?
2) What is the proper way of doing this, and what would the right MySQL query look like?

Edit:

To be clear, I have no problem whatsoever getting which shop has which stock. The problem I'm having is showing all the stock that shop 1 has + all the stock shop 1 does not have.
My query:
{-code-4}
{-code-5}
{-code-6}

This query returns:

{-code-7}

If I add{-code-2} shop <> '2', the NULL also disappears. I want a query that will return the following table for shop 1:

{-code-9}

628

Answer

--- | 1 | Head Office | 2 | shop 1 *Stock table: | id | stock_description

350

Answer

---- | 1 | Some description | 2 | Some description | 3 | Some description *Stock amounts table: | id | shop_id | stock_id | stock_amount

440

Answer

-- | 1 | 1 | 1 | 5 | 2 | 2 | 2 | 4|||WHERE|||WHERE stock_amounts.id <> '2'|||SELECT shop, stock.id stock_id, description, amount|||FROM stock|||LEFT JOIN shop_stock ON shop_stock.stock_id = stock.id||||shop|stock_id|description |amount

462

Answer

-------- |1 |1 |Some description|5 |2 |2 |Some description|4 |NULL|3 |Some description|NULL|||WHERE shop <> '2'||||shop|stock_id|description |amount

328

Answer

-------- |1 |1 |Some description|5 |NULL|2 |Some description|NULL |NULL|3 |Some description|NULL

371

Answer

Solution:

You want to use an outer join instead of an inner join. If you provided your query, it would be much easier to tell.

You may be using an outer join. In that case, the condition in the where clause is requiring thatstocks actually have a match. Remember thatNULL <> 2 evaluates to false, not true.

You fix this by putting the condition in theon clause.

To get the full panoply of shops and stocks, use a driver table that produces all combinations and then join this to the amounts:

select name, stock_description, coalesce(sa.stock_amount, 0) as stock_amount
from (select shop.id as shop_id, shop.name, stock.id as stock_id, stock.stock_description
      from shop cross join stock
     ) ss left outer join
     stockamount sa
     on sa.shop_id = ss.shop_id and sa.stock_id = ss.shop_id

If you want to exclude 2, do this in the ss table.

select name, stock_description, coalesce(sa.stock_amount, 0) as stock_amount
from (select shop.id as shop_id, shop.name, stock.id as stock_id, stock.stock_description
      from shop cross join stock
      where stock.id <> 2
     ) ss left outer join
     stockamount sa
     on sa.shop_id = ss.shop_id and sa.stock_id = ss.shop_id

Answer

Solution:

I think that you want to make a left join onAmounts with the cross product ofShop andStock, which will produce the required null values on stock entries which are non existent for each shop.:

SELECT Shop.*, Stock.*, Amounts
FROM Shop
CROSS JOIN Stock
LEFT JOIN Amounts
ON Shop.id = shop_id
AND Stock.id = stock_id

People are also looking for solutions to the problem: php - TRUNCATE does not work

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question