Arpit Porwal Arpit Porwal - 2 months ago 8
MySQL Question

MySQL : selecting minimum value from one table and country details from another and grouping them according to country code

I have two tables in my database:

1. venprices
+---------+---------+--------+
| concode | rate | vendor |
+---------+---------+--------+
| 1234 | 1.23402 | a |
| 1234 | 1.5692 | b |
| 1234 | 1.114 | c |
| 1234 | 1.4 | d |
| 1122 | 1.46 | a |
| 1122 | 1.5 | c |
| 1122 | 1.2 | d |
+---------+---------+--------+

2. country
+-------+---------+
| conid | conname |
+-------+---------+
| 1122 | con2 |
| 1234 | con1 |
+-------+---------+


I want an output like this:

+---------+---------+-------------+--------+
| concode | conname | MIN(v.rate) | vendor |
+---------+---------+-------------+--------+
| 1122 | con2 | 1.2 | d |
| 1234 | con1 | 1.114 | c |
+---------+---------+-------------+--------+


I am Using Query :
SELECT v.concode,c.conname, MIN(v.rate), v.vendor FROM venprices v, country c WHERE c.conid = v.concode GROUP BY c.conid;


The query gives the result but with a wrong vendor name. Instead of delivering the rate relative vendors, it always giving me the vendor
a
in the result.

I've tried different clauses and subqueries but it went in vein.
Can someone please tell me the actual problem and the query to produce the desired result?

Answer

You could use a suquery

select v.concode,c.conname, v.rate, v.vendor
FROM venprices v
inner join  country c on c.conid = v.concode 
where (v.concode, v.rate) in ( select v.concode, min(v.rate)
                                  from venprices v 
                                  group by v.concode)

if there isn't 'conid' related to 'concode', you can use LEFT JOIN

select v.concode,c.conname, v.rate, v.vendor
FROM venprices v
left join  country c on c.conid = v.concode 
where (v.concode, v.rate) in ( select v.concode, min(v.rate)
                                  from venprices v 
                                  group by v.concode)