I'm not quite sure how to best word this question. I don't necessarily need a way to eliminate rows that contain a column possessing a null value -- I just need to figure out how to adjust my query so it's only generating the single row result for each user that I need... Here's some background info to help clarify:
Alright, so I have 5 tables: users, local_ads (local ads), local_rev (local revenue report), nat_ads (national ads), and nat_rev (national revenue report).
Every month, a user submits a National Revenue Report and a Local Revenue Report. When the user creates their monthly reports, they are given a unique report ID for both local and national reports. The report_id and the corresponding user_id is stored in the their respective tables (local_rev or nat_rev).
The user then submits Ads to each report. The local Ads submitted for the month's Local Report are stored in the local_ads table, and for a given user, each of their Ads share the same report_id. The same is true for National Ads.
Every Ad in both the local_rev and nat_rev tables have a column for price and size.
I am trying to execute a query that will generate the Local Ad total size, Local Ad quantity, Local Ad total price, National Ad total size, National Ad quantity, and National Ad total price for each user WHERE month="x."
Here is the query that has come the closest to what I need:
SELECT u.franchise, lr.lrr_id, CountLocId, TotalPrice, nr.nrr_id, CountNatId, TotalNMoney,
(
TotalPrice + TotalNMoney
)TotalRev
FROM users u
LEFT JOIN local_rev lr ON u.user_id = lr.user_id
LEFT JOIN (
SELECT lrr_id, COUNT( lad_id ) CountLocId, SUM( price ) TotalPrice
FROM local_ads
GROUP BY lrr_id
)la ON lr.lrr_id = la.lrr_id
LEFT JOIN nat_rev nr ON u.user_id = nr.user_id
LEFT JOIN (
SELECT nrr_id, COUNT( nad_id ) CountNatId, SUM( tmoney ) TotalNMoney
FROM nat_ads
WHERE MONTH = 'February'
GROUP BY nrr_id
)na ON nr.nrr_id = na.nrr_id
WHERE lr.month = 'February'
ORDER BY franchise
LIMIT 0 , 30
The problem is that it is generating a row for each user, for every month instead of for a single month. You can see this in the below results. In the results, the rows that possess no NULL values are the rows that I want. So I just need to figure out how to eliminate the rows with NULL values.
userA 84 39 5200 158 NULL NULL NULL
userA 84 39 5200 53 NULL NULL NULL
userA 84 39 5200 182 NULL NULL NULL
userA 84 39 5200 81 5 5900 11100
userB 93 1 100 51 NULL NULL NULL
userB 93 1 100 200 NULL NULL NULL
userB 93 1 100 84 9 5742 5842
So, I've tried using dozens of different examples that I've found here on Stackoverflow, among other sources. I just can't get any to work when applied to my specific query. I've spent hours making random adjustments, hoping to get the result I need, but to no luck. Posting here is my absolute last resort!
Sorry for being so long-winded, I just wanted to be sure I provided all the information needed to help solve my problem. Thank you so much for your help.
