user5400828 user5400828 - 1 month ago 5
SQL Question

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

Answer
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)