Suppose I have two tables.
Prod_name Quantity A 15 B 5 C 17
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.
Med_name Quantity B 5
Note - using string to
LEFT JOIN the tables
ON isn't great - try to use a
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) )