-4

I have two tables per below: 

1. DatesMaster

+----------+---------------+
|  m_date  |    capacity   |
+----------+---------------+
| 1/2/2016 |          10   |
| 2/2/2016 |          10   |
| 3/2/2016 |          10   |
+----------+---------------+

2. Schedule

+----------+-------+
| sch_date | qty   |
+----------+-------+
| 1/2/2016 |   2   |
| 1/2/2016 |   2   |
| 1/2/2016 |   2   |
| 2/2/2016 |   5   |
| 2/2/2016 |   5   |
| 3/2/2016 |   5   |
| 3/2/2016 |   1   |
+----------+-------+

How do I get the result of capacity - qty using left outer join whereby I can display all dates from DatesMaster with Availability as the difference between capacity and sum of qty of same dates?

Morpheus
  • 1,616
  • 1
  • 21
  • 31
Subi
  • 3
  • 1
  • 4
  • 1
    i am using access in vb.net – Subi Feb 03 '16 at 05:25
  • 1
    i tried: SELECT DatesMaster.m_date, DatesMaster.capacity - SUM(Schedule.qty) As Balance FROM DatesMaster LEFT OUTER JOIN Schedule ON DatesMaster.m_date = Schedule.sch_date GROUP BY DateMaster.m_date – Subi Feb 03 '16 at 10:08
  • Have you attempted to resolve the NULLs? – Morpheus Feb 05 '16 at 15:33
  • 1
    yes i did and got it right! i used IIf and Is Null and it worked. Nz() did not work.... Thanks a lot for your advise! – Subi Feb 05 '16 at 16:43
  • I'm glad I could help. Remember, if the answer was useful you have the option to [accept](http://stackoverflow.com/help/accepted-answer) it. – Morpheus Feb 09 '16 at 16:00
  • just did it. in fact i am new to this platform and new to programming! – Subi Feb 10 '16 at 06:01
  • I think you will find this site very valuable on your journey into the world of programming. Enjoy! – Morpheus Feb 10 '16 at 15:49

1 Answers1

1

Any columns in your SELECT that are not in the GROUP BY must be contained in an aggregate function so you can use MAX in this query.

SELECT DatesMaster.m_date
    ,MAX(DatesMaster.capacity) - SUM(Schedule.qty) AS Balance
FROM DatesMaster
LEFT JOIN Schedule ON DatesMaster.m_date = Schedule.sch_date
GROUP BY DatesMaster.m_date

Or, add the capacity column to the GROUP BY

SELECT DatesMaster.m_date
    ,(DatesMaster.capacity) - SUM(Schedule.qty) AS Balance
FROM DatesMaster
LEFT JOIN Schedule ON DatesMaster.m_date = Schedule.sch_date
GROUP BY DatesMaster.m_date, DatesMaster.capacity
Morpheus
  • 1,616
  • 1
  • 21
  • 31
  • above did not work, got the error: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: You tried to execute a query that does not include the specified expression 'mdate' as part of an aggregate function. – Subi Feb 03 '16 at 20:03
  • in fact my original DatesMaster table contains m_date that are not present in the schedule table.... DatesMaster container dates starting 1-2-2016 till 31-12-2016 where as Schedule has dates 1-2-2016, 2-2-2016 & 3-2-2016 but duplicated.... – Subi Feb 03 '16 at 20:10
  • In your original query, you had a typo in the `GROUP BY`. Can you make sure that isn't the cause of the error. Change `DateMaster.m_date` to `DatesMaster.m_date`. You were missing an "s". – Morpheus Feb 03 '16 at 20:37
  • 1
    You are absolutely right!!! I made a typo.... Thanks a lot!!! Now it is giving me the result in the Balance column but just for the ones matching schedule table... Other DatesMaster.m_date s are being displayed but corresponding Balance is Null whereas logically i want to show DatesMaster.capacity as it is in the table.... (DatesMaster table contains m_date that are not present in the schedule table) Could you advise how to do that? – Subi Feb 03 '16 at 21:15
  • For MS Access you can find some possible options here: http://stackoverflow.com/a/247872/3854195 – Morpheus Feb 03 '16 at 22:38