-1

I have two tables:

Suppliers:

+-------+------+---------------+
| supid | prid | supplier_name |
+-------+------+---------------+
|     2 |    2 | Supplier 1    |
|     3 |    2 | Supplier 2    |
|     4 |    2 | Supplier 3    |
+-------+------+---------------+

Supplier_items:

+-----------+------+-------+--------+------------+
| supitemid | prid | supid | itemid | prod_tcost |
+-----------+------+-------+--------+------------+
|         3 |    2 |     2 |      3 |       6200 |
|        4  |    2 |     2 |      4 |        810 |
|         5 |    2 |     3 |      3 |       5900 |
|         6 |    2 |     3 |      4 |        807 |
|         7 |    2 |     4 |      3 |       6680 |
|         8 |    2 |     4 |      4 |        825 |
+-----------+------+-------+--------+------------+

Please help me to achieve this result getting the min value with supplier_name from Suppliers: (where prid = 2, group by itemid)

+--------+---------------+----------------+
| itemid | supplier_name | min(prod_tcost) |
+--------+---------------+----------------+
|      3 | Supplier 2    |           5900 |
|      4 | Supplier 2    |            807 |
+--------+---------------+----------------+
peter talandron
  • 1
  • 1

1 Answers1

0

You can join and aggregate:

select si.item_id, s.supplier_name, min(si.prod_tcost) min_prod_tcost
from supplier_items si
inner join suppliers s on s.supid = si.supid
where si.item_id in (3, 4) and si.prid = 2
group by si.item_id, s.supid, s.supplier_name
GMB
  • 216,147
  • 25
  • 84
  • 135
  • +1. thanks for answering. your answer list all the suppliers group by "itemid" without getting the min value. I want to achieve the minimum value based on itemid=3 and itemid=4 and prid=2 – peter talandron Jul 28 '20 at 08:19
  • @petertalandron: just add a `where` clause. See my updated answer. – GMB Jul 28 '20 at 08:22
  • the result produced 6 rows group by itemid 3 and 4. Listed 3 Supplier_name and Prod_tcost with itemid=3 and another 3 rows when itemid=4 – peter talandron Jul 28 '20 at 08:42
  • i can get the min(Prod_tcost) group by itemid. it is difficult to add its corresponding supplier_name of the lowest prod_tcost – peter talandron Jul 28 '20 at 08:46