How to compare column from two tables?

2916 views c#
-1

Suppose I have two tables.

Table_1:

Prod_name Quantity
A         15
B         5
C         17

Table_2:

Prod_name  threshold
A          7
B          6
C          8

How can I compare these two tables in sql , so that particular prod_name is displayed if its quantity falls below threshold level from another table.

Expected output:

Med_name  Quantity
B         5

answered question

You should not expect Stack Overflow to be a code-writing service.

@Milen Please do not destroy questions by editing it to unreadability.

@UweKeim I was not my intention

2 Answers

0

Try this.

SELECT A.Prod_name AS Med_name,A.Quantity FROM Table_1 A INNER JOIN Table_2 B ON 
    A.Prod_name=B.Prod_name WHERE A.Quantity < B.threshold

posted this
4

Try this.

Note - using string to LEFT JOIN the tables ON isn't great - try to use a Product_Code or ID if you know they're the same in both tables.

Using the info that I know about your table structure though - something like this should do the trick.

SELECT a.Prod_name AS Med_name
     , CASE WHEN a.Quantity < b.Quantity 
            THEN a.Quantity 
            ELSE b.Quantity
  FROM ( 
         SELECT Prod_name
              , Quantity
           FROM Table_1
       ) a
  LEFT 
  JOIN ( 
         SELECT Prod_name
              , Quantity
           FROM Table_2
       ) b
    ON ( LOWER(a.Prod_Name) = LOWER(b.Prod_Name) )

posted this

Have an answer?

JD

Please login first before posting an answer.