CODEWITHSUNDEEP
mysql
Al Kasih
Al Kasih

Reputation: 886

return null if one of field is not exist mysql

$check = "SELECT    up.quantity AS stockAble,
                    oh.quantity AS stockAsk

          FROM      stock_update up
          JOIN      orders oh
          ON        up.quantity > oh.quantity
          AND       up.package = oh.product_id

          WHERE     oh.reference_no = 12345 
          AND       up.location_id = 4                      
          ";

In the query, I select all the quantity of a package based on the reference no.

In the reference number of 12345, for instance, there are three rows of three products with each different quantity.

Stock Ask in oh.quantity From orders

  +---------------+--------------+------------+
    reference_no     product_id     quantity
  +---------------+--------------+------------+
      12345             1             30
      12345             2             10
      12345             3             20

However it needs to check if the quantity or the product which is asked is available in our table.

Stock Available in stock_update

  +--------------+------------+
    product_id     quantity
  +--------------+------------+
         1             10
         2             15
         3             25

Based on the two tables above, there is one product which its available quantity is less than the quantity which is asked. It is in the row or in the product_id of 1

If the condition is so, then I want the query return nothing or false.

The query will return true if only all the product and the quantity which is asked is available.

How to do that in the query I have tried above? Thank you very much for the help.

Upvotes: 2

Views: 823

Answers (2)

Israel Gelover
Israel Gelover

Reputation: 9

Please try this query, this returns 0 if any of the products does not have enough quantity in stock:

SELECT  (SUM(CASE WHEN up.quantity >= oh.quantity THEN 1 ELSE 0 END) = COUNT(*)) AS inStock
        #up.quantity AS stockAble, oh.quantity AS stockAsk
FROM    stock_update up
JOIN    orders oh
ON      up.product_id = oh.product_id
#AND        up.quantity >= oh.quantity
WHERE   oh.reference_no = 12345;

Upvotes: 1

Fabian Schmengler
Fabian Schmengler

Reputation: 24576

After your comment, I get what you want and I think it's possible with some tricky subqueries but not sensible.

I'd rather perform two queries then:

  1. find out if there is a case where up.quantity < oh.quantity

    SELECT 1
  FROM      stock_update up
  JOIN      orders oh
  ON        up.quantity < oh.quantity
  AND       up.package = oh.product_id

  WHERE     oh.reference_no = 12345 
  AND       up.location_id = 4
  LIMIT 1

  2. if not (i.e. the previous query does not return any result), perform the original query

Upvotes: 1

Related Questions