CODEWITHSUNDEEP
mysqlsql
user5400828
user5400828

Reputation: 71

Finding the minimum value in sql

Ok so this table is concerning a charity. It has three tables. 1)donor 2) gift and 3) year

I have to find the minimum value in the year 2000 displaying the first and last name of the donors. I am able to display the minimum amount for the year 2000, however the name of the donor is not right. This is what I have so far:

SELECT DONOR.DONOR_LNAME, DONOR.DONOR_FNAME, MIN( GIFT.AMOUNT ) 
FROM DONOR
JOIN GIFT ON DONOR.DONOR_NO = GIFT.DONOR_NO
WHERE YEAR_NUM =2000
HAVING MIN( GIFT.AMOUNT ) 
LIMIT 0 , 30

Please help

Donor table

enter image description here

Gift table

enter image description here

Upvotes: 0

Views: 122

Answers (3)

A J
A J

Reputation: 4024

After looking at the table structures, you should try following Query.

SELECT DONOR.DONOR_NO, DONOR.DONOR_LNAME, DONOR.DONOR_FNAME, GIFT.AMOUNT
FROM DONOR INNER JOIN GIFT ON DONOR.DONOR_NO = GIFT.DONOR_NO
WHERE GIFT.YEAR_NUM =2000
HAVING MIN(GIFT.AMOUNT)
LIMIT 0, 30

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

SELECT t1.DONOR_FNAME AS first_name,
       t1.DONOR_LNAME AS last_name,
       t2.AMOUNT AS min_donation
FROM DONOR t1
INNER JOIN GIFT t2
    ON t1.DONOR_NO = t2.DONOR_NO
WHERE t2.YEAR_NUM = 2000 AND
      t2.AMOUNT = (SELECT MIN(AMOUNT) FROM GIFT WHERE YEAR_NUM = 2000 GROUP BY YEAR_NUM)

Upvotes: 2

Zeina
Zeina

Reputation: 1603

Add Group by to your query, but not sure about the limit thing...

SELECT DONOR.DONOR_LNAME, DONOR.DONOR_FNAME, MIN( GIFT.AMOUNT) AS MinAmount
FROM DONOR
JOIN GIFT ON DONOR.DONOR_NO = GIFT.DONOR_NO
WHERE YEAR_NUM =2000
GROUP BY DONOR.DONOR_LNAME, DONOR.DONOR_FNAME

Upvotes: 0

Related Questions