I've 3 tables

bl_main (bl_id UNIQUE, bl_area)
bl_details (bl_id UNIQUE, name) 
bl_data(bl_id, month, paper_tons, bottles_tons)

bl_id isn't unique within the last table. You will see multiple rows of same bl_id.

I'm attempting to retrieve data within the following way

bl_id | name | bl_area | sum(paper_tons) | sum (bottles_tons) | paper_tons | bottles_tons

sum(paper_tons) should return the sum of the all of the paper tons for the similar bl_id like Jan to December.

While using below query i can retrieve all of the data properly except within the result, you will find multiple occurances of bl_ids(From bl_data table).

SELECT bl_main.bl_id,name,bl_area,sums.SummedPaper, sums.SummedBottles,paper_tons,bottles_tons
FROM bl_main
JOIN bl_details ON 
    bl_main.bl_id= bl_details.bl_id
left outer JOIN bl_data ON
    bl_data.bl_id= bl_main.bl_id
left outer JOIN (
    SELECT bl_id, SUM(Paper_tons) As SummedPaper, SUM(bottle_tons) As SummedBottles 
FROM bl_data

GROUP by bl_id) sums ON sums.bl_id = bl_primary.bl_id

I wanto retrieve just the unique values of bl_ids without repetition also it should retain the bl_id that has the max month and never all of the several weeks for the similar bl_id.

For ex:

INCORRECT


**0601**    University Hall 	75.76 	17051 	1356 	4040 	1154 	**11**     
**0601**    University Hall 	75.76 	17051 	1356 	9190 	101 	**12**  
**0605**    UIC Student 	22.86 	3331 	14799 	0 	356   **8**   

CORRECT   
**0601**    University Hall 	75.76 	17051 	1356 	9190 	101 	**12**  
**0605**    UIC Student 	22.86 	3331 	14799 	0 	356   **8**

I understand I'm able to obtain the max value using

WHERE Month = (SELECT MAX(Month)

but where exactlt must i add this within the query and really should i alter the join definition. Any assistance is highly appreciated like me a new comer to sql. Thanks ahead of time.

You've two tables that most likely ought to be combined into one (bl_main and bl_details). But putting that aside, the thing you need is really a self-join subquery to choose the row using the max month. Something similar to the next (untested):

SELECT bl_main.bl_id, bl_details.name, bl_main.bl_area, sums.sum_paper_tons,
       sums.sum_bottles_tons, maxmonth.paper_tons, maxmonth.bottles_tons
FROM bl_main
INNER JOIN bl_details ON bl_main.bl_id = bl_details.bl_id
LEFT OUTER JOIN (SELECT bl_id, SUM(paper_tons) AS sum_paper_tons, 
                        SUM(bottles_tons) AS sum_bottles_tons
                 FROM bl_data
                 GROUP BY bl_id) sums ON bl_main.bl_id = sums.bl_id
LEFT OUTER JOIN (SELECT bl_id, paper_tons, bottles_tons
                 FROM bl_data data2
                 INNER JOIN (SELECT bl_id, MAX(month) AS max_month
                             FROM bl_data
                             GROUP BY bl_id) m
                    ON m.bl_id = data2.bl_id
                    AND m.max_month = data2.month) maxmonth
    ON bl_main.bl_id = maxmonth.bl_id

You are able to join the table that contains the month against itself, utilizing a subquery from the form:

Select *
From mytable m
    Inner Join (Select max(Month) as Month, myId
                From mytable
                Group By myId) mnth
        On mnth.myId = m.myId and mnth.Month = m.Month

Your JOIN clause

left outer JOIN bl_data ON
  bl_data.bl_id= bl_main.bl_id

doesn't specify which month to choose for that data you're exhibiting with paper_tons and bottles_tons.

You can update that JOIN to simply retain the max month, which should limit the records, like so:

left outer JOIN (SELECT bl_id, MAX(Month) as Month from bl_data GROUP BY bl_id) as Month
  ON Month.bl_id = bl_main.bl_id
left outer JOIN bl_data ON
  bl_data.bl_id = bl_main.bl_id AND bl_data.Month = Month.bl_Month

I believe this question is what you're searching for

SELECT bl_main.bl_id,name, bl_area, sums.SummedPaper, sums.SummedBottles, paper_tons, bottles_tons
FROM bl_main
    JOIN bl_details ON bl_main.bl_id= bl_details.bl_id
    left outer JOIN bl_data ON    bl_data.bl_id= bl_main.bl_id
    left outer JOIN
    (
        SELECT bl_id, month, SUM(Paper_tons) As SummedPaper, SUM(bottle_tons) As SummedBottles 
        FROM bl_data
        WHERE month in
        (SELECT MAX(month) FROM bl_data GROUP BY bl_id)
        GROUP BY bl_id, month
    ) sums ON sums.bl_id = bl_main.bl_id