return null if one of field is not exist mysql

Question

$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.

What's the actual vs the expected result? Your query looks OK, except that you probably want to use >= instead of > — Fabian Schmengler
– Fabian Schmengler, Commented Feb 4, 2015 at 22:24
@fschmengler My query is still return two rows because one row is not exist. what I want is if one of the three is not exist, then I want the query no to turn any row. — Al Kasih
– Al Kasih, Commented Feb 4, 2015 at 22:25
Based on your sample data, your query returns null aka no row. except that you probably want to use >= instead of > — Hotdin Gurning
– Hotdin Gurning, Commented Feb 4, 2015 at 22:28
I am sorry. I forgot to change the quantity. Please look at again at the table. — Al Kasih
– Al Kasih, Commented Feb 4, 2015 at 22:33

Fabian Schmengler · Accepted Answer · 2015-02-04 22:33:26Z

1

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:

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

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

answered Feb 4, 2015 at 22:33

Fabian Schmengler

24.6k9 gold badges82 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Al Kasih Over a year ago

Thank you for the solution. But with the query above, now if all stocks are available, it doesn't return any row too.

Fabian Schmengler Over a year ago

That's the point. As I said, if this does not return any result, perform the original query

Al Kasih Over a year ago

How to do that? Please help again.

Fabian Schmengler Over a year ago

With an if statement in your favorite programming language?

Al Kasih Over a year ago

I am using php. But I still not got the idea of what you mean since the query above will always return no row in any condition.

|

Israel Gelover · Accepted Answer · 2015-02-04 23:29:10Z

1

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;

answered Feb 4, 2015 at 23:29

Israel Gelover

92 bronze badges

2 Comments

Al Kasih Over a year ago

How about the performance of this query. Because when I run this query on the page its result is not as accurate as if i run this on the workbench.

Israel Gelover Over a year ago

I'm sorry @klaudia, I don't understand how the query is not as accurate when run on the page as when run on the workbench. Does it gives different results? The performance will significantly depend on your database, indexes and so on. Here we are clearly leaving many constraints outside and focusing on your original problem. Hope this helps.

Collectives™ on Stack Overflow

return null if one of field is not exist mysql

2 Answers 2

9 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related