CODEWITHSUNDEEP
mysqlsql
user8143979
user8143979

Reputation: 127

How to match two different tables in sql query?

I have these two kinds of table below:

 Stocks Table          Order Table
-------------         -------------
| stockid   |         | orderid   |
| stockname |         | orderqty  |
| stockqty  |         | stockid   |
-------------         -------------

What I want to do is when orderqty is greater than stockqty I want to display a message saying that stocks are not enough, and when orderqty is less than stockqty then it is done.

The main concern here is to identify wether orderqty is less than or greater than stockqty.

Upvotes: 1

Views: 64

Answers (2)

Jon Jaussi
Jon Jaussi

Reputation: 1296

Please see this DB Fiddle and let me know if it helps.

https://www.db-fiddle.com/f/hgEdnyeTTuxamXna9ELL3P/2

CREATE TABLE t_stock (
  stockid INT,
  stockname VARCHAR(50),
  stockqty BIGINT
);
INSERT INTO t_stock (stockid, stockname, stockqty) VALUES (1, 'salmon', 150);
INSERT INTO t_stock (stockid, stockname, stockqty) VALUES (2, 'asparagus', 275);
INSERT INTO t_stock (stockid, stockname, stockqty) VALUES (3, 'lemon', 300);


CREATE TABLE t_order (
  orderid INT,
  orderqty BIGINT,
  stockid INT
);
INSERT INTO t_order (orderid, orderqty, stockid) VALUES (13, 150, 3);
INSERT INTO t_order (orderid, orderqty, stockid) VALUES (14, 275, 2);
INSERT INTO t_order (orderid, orderqty, stockid) VALUES (15, 300, 1);


SELECT o.orderid
      ,o.stockid
      ,o.orderqty
      ,s.stockqty
      ,CONCAT(CASE WHEN o.orderqty > s.stockqty
                   THEN 'stocks are not enough for '
                   WHEN o.orderqty = s.stockqty
                   THEN 'stocks are just enough for '
                   ELSE 'we have plenty of stock for '
                   END, s.stockname) as stockqty_analysis
  FROM t_stock s INNER JOIN t_order o
    ON s.stockid = o.stockid;

Upvotes: 1

Indominus
Indominus

Reputation: 1248

SELECT o.orderid, 
       o.orderqty,
       s.stockqty,
       'stocks are not enough' AS message 
FROM orders o
JOIN stocks s ON o.stockid = s.stockid
WHERE o.orderqty < s.stockqty

Upvotes: 1

Related Questions