0

I'm pretty stuck on this query. First of all, what do I want to achieve? I want to inner join on three tables (already correct) and filter out data depending if the date is in the current month. If the date is not in the current month the field "bedrag" shouldn't be filled in and should be set to zero. I should also see everything from the table, but the 'bedrag' field shouldn't be changed.

If the date IS from the current month it should add to 'bedrag' (note the group by to do that) and show everything too. Now my query does everything correct EXCEPT for when you have data thats not from this month. Then records will go lost / won't be showed.

Table design: https://i.stack.imgur.com/zrFGq.jpg

The query:

SELECT        c.id, c.omschrijving, l.maximumBedrag, SUM(IIF(IsNULL(f.bedrag), 0, f.bedrag)) AS bedrag
FROM            ((Categorie c LEFT OUTER JOIN
                         Financien f ON f.categorieId = c.id) LEFT OUTER JOIN
                         Limiet l ON l.categorieId = c.id)
WHERE        (f.inkomstOfUitgave IS NULL) OR
                         (f.inkomstOfUitgave = 1)  AND (format(f.datum, 'yyyy-mm-dd') > format(NOW(), 'yyyy-mm'))
GROUP BY c.id, f.categorieId, c.omschrijving, l.maximumBedrag

Do note: this is NOT normal SQL, but a varient thats used in C# to get data from an Access DB into an DAL and BLL layer.

An example where you can see what messages with the query and HOW it goes wrong: https://i.stack.imgur.com/vsuy1.jpg

Could anybody tell me how I can get all the data to show but without the bedrag added onto it if the records aren't from this month?

Thanks!

Yenthe
  • 2,313
  • 5
  • 26
  • 46

1 Answers1

0

Maybe move some conditions from WHERE to LEFT OUTER JOIN?

SELECT c.id, sum(isnull(f.bedrag, 0))
FROM @cat c
LEFT OUTER JOIN @fin f ON f.catid=c.id AND f.datum > getdate()
LEFT OUTER JOIN @lim l ON l.catid = c.id

Check this SQL Fiddle

Tomasito
  • 1,864
  • 1
  • 20
  • 43
  • Sadly it isn't normal SQL, its another version of it (not quite sure which but its used for C# to Access) so I can't use those @ tags, neither can I only the normal dates etc.. Could you explain me how to put that left outer join in my query? :/ – Yenthe May 08 '13 at 08:04
  • @Yenthe those @ tags are from sql fiddle that mimics yours DB schema. Just move `AND (f.inkomstOfUitgave = 1) AND (format(f.datum, 'yyyy-mm-dd') > format(NOW(), 'yyyy-mm'))` from `WHERE` to `LEFT OUTER JOIN` condition `Financien f ON f.categorieId = c.id AND ....` – Tomasito May 08 '13 at 08:57
  • do you mean like this? SELECT c.id, c.omschrijving, l.maximumBedrag, SUM(IIF(IsNULL(f.bedrag), 0, f.bedrag)) AS bedrag FROM ((Categorie c LEFT OUTER JOIN Financien f ON f.categorieId = c.id AND (format(f.datum, 'yyyy-mm-dd') > format(NOW(), 'yyyy-mm'))) LEFT OUTER JOIN Limiet l ON l.categorieId = c.id) WHERE (f.inkomstOfUitgave IS NULL) OR (f.inkomstOfUitgave = 1) GROUP BY c.id, f.categorieId, c.omschrijving, l.maximumBedrag This gives me the error 'join expression isn't supported' :/ – Yenthe May 08 '13 at 16:30
  • @Yenthe Can you create a view in the database with the query? This would bypass restrictions of Acces / C #. Then select the data from the view instead. – Tomasito May 08 '13 at 19:01
  • sadly this is for a schoolassingment and I HAVE to do it on this way :( So I'm afraid I can't (and probably not even if I was allowed) – Yenthe May 09 '13 at 10:36