I need to write a query to display the customer code, customer first name, last name, full address, invoice date, and invoice total of the largest purchase made by each customer in Alabama (including any customers in Alabama who have never made a purchase; their invoice dates should be NULL and the invoice totals should display as 0). Here is an ERD of the two required tables. Here is what my latest query looks like.
select distinct lgcustomer.cust_code ,Cust_FName ,Cust_LName ,Cust_Street ,Cust_City ,Cust_State ,Cust_ZIP ,max(inv_total) as [Largest Invoice] ,inv_date from lgcustomer left join lginvoice on lgcustomer.cust_code = lginvoice.cust_code where Cust_State = 'AL' group by lgcustomer.cust_code ,Cust_FName ,Cust_Lname ,Cust_Street ,Cust_City ,Cust_State ,Cust_ZIP ,Inv_Date I don't understand why, despite using DISTINCT lgcustomer.cust_code as well as only the MAX(inv_total), it still returns every inv_total for that customer. My professor says to make use of UNION, but as I understand it, that is for compiling two different tables with the same attributes. I appreciate any responses that can point me in the right direction! Solution The answer we came to in class was to use a correlated subquery and union.
select c.cust_code ,cust_fname ,cust_lname ,cust_street ,cust_city ,cust_state ,inv_date ,inv_total from lgcustomer c left outer join lginvoice i on c.cust_code = i.cust_code where cust_state = 'AL' and inv_total = (select max(inv_total) from lginvoice i2 where i2.cust_code = c.cust_code) union select c.cust_code ,cust_fname ,cust_lname ,cust_street ,cust_city ,cust_state ,'' ,0 from lgcustomer c left outer join lginvoice i on c.cust_code = i.cust_code where cust_state = 'AL' and inv_date is null and inv_total is null order by cust_lname asc