Reverse SQL query possible?

Question

There is a table called suppliers with following data:

    supplier_id minimum_order_price
    1           100
    2           55
    4           33

There is a table called suppliers_products with following data: Assuming product_id is only with one supplier.

    supplier_id product_id
    1           3237022467
    1           3298604931
    2           1792078659
    2           3237022083
    4           1963023491

One supplier_id may have many product_id.

I have a data in such a way that product_id and its price from some third party. Example:

product_id      price
3237022467      45
3298604931      56
1792078659      78
3237022083      123
1963023491      65

Is this possible to show only those results which product's price is greater than its suppliers minimum_order_price?

Assuming product_id is only with one supplier.

Answer 1

Try this solution

SELECT s.supplier_id,
       s.minimum_order_price,
       sp.product_id,
       pp.price
FROM suppliers s
     INNER JOIN suppliers_products sp ON s.supplier_id = sp.supplier_id
     INNER JOIN product_prices pp ON pp.product_id = sp.product_id
WHERE s.minimum_order_price < pp.price

Answer 2

May be this will help you

select suppliers_products.* from suppliers_products

left join suppliers
on suppliers.supplier_id = suppliers_products
.supplier_id
left join product
on suppliers_products.product_id = product.product_id

where product.price > suppliers.minimum_order_price

Answer 3

select third_party_table.product_id, third_party_table.price from
     third_party_table, suppliers_products, suppliers 
where third_party_table.product_id = suppliers_products.product_id 
      and suppliers_products.supplier_id  = suppliers.supplier_id 
      and third_party_table.price> suppliers.price