-1

Here's a simple example: sqlfiddle.com/#!9/8087c5/1

I've got the following query:

select
    contracts.ID as contractID,
    departments.ID as departmentID,
    max(invoices.timestamp) as timestamp
from contracts
    left join departments on departments.ID = contracts.departmentID and departments.accountingType > 0
    left join invoices on contracts.ID = invoices.contractID
group by contractID
having departmentID is not null;

I am expecting to get a result as follows:

+------------+--------------+---------------------+
| contractID | departmentID | timestamp           |
+------------+--------------+---------------------+
| 101        | 301          | NULL                |
+------------+--------------+---------------------+
| 102        | 302          | NULL                |
+------------+--------------+---------------------+
| 103        | 303          | 2020-05-01 11:11:00 |
+------------+--------------+---------------------+

instead I get:

+------------+--------------+---------------------+
| contractID | departmentID | timestamp           |
+------------+--------------+---------------------+
| 103        | 303          | 2020-05-01 11:11:00 |
+------------+--------------+---------------------+

I do not understand why the query cuts the NULL Values of the last left joined table. Am I missing something very simple?

renegade999
  • 84
  • 8
  • 1
    Welcome to SO. Please see [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Jun 24 '20 at 08:58
  • 1
    Different queries produce different results. It seems almost beyond the scope of SO to explain why. Note that a GROUP BY clause should always include ALL non-aggregated columns included within a SELECT. – Strawberry Jun 24 '20 at 09:03
  • Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. – philipxy Jun 24 '20 at 09:51
  • You improved this a lot, although we still can't cut & paste & run and this is not minimal & you didn't identify & chop down to the 1st bad expression etc. But you are able to characterize your bug--a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy Jun 24 '20 at 10:04
  • I'm expecting it to be there since I do **not** want results with NULL values in column departmentID **but** in column timestamp. For my understanding I'm not excluding any NULL values of column timestamp. If I'm removing the having out of my statement I'm still not getting the NULL values. Please let me build an example on sql-fiddle. That might take me a couple minutes. – renegade999 Jun 24 '20 at 10:12
  • sqlfiddle.com/#!9/8087c5/1 Here's an example of what I'm doing. I don't understand why i'm not getting a row with contractID 3, departmentID 2 and timestamp NULL Does that help solving my Problem? – renegade999 Jun 24 '20 at 11:52
  • Put the code in your post, not just at a link. Again: Say why you expect what you expect, including justifying it by referencing an SQL manual/introduction. Because you get what you get because of how SQL works. We need to know what you misconception is. Again: Did you understand why Strawberry warned you re group by columns? [Error related to only_full_group_by when executing a query in MySql](https://stackoverflow.com/q/34115174/3404097) Again: What is the *first* place you don't get what you expect. Again: *Minimal* code. Etc. There is still no [mre] here. Clarify via edits, not comments. – philipxy Jun 24 '20 at 12:10
  • See [How do comment @replies work?](https://meta.stackexchange.com/questions/43019/how-do-comment-replies-work) to learn to use @x to notify one non-sole non-poster commenter x re a comment. – philipxy Jun 24 '20 at 12:12

1 Answers1

0

Alright - Problem was the group by contractID correct would have been group by contracts.ID. The group by contractID grouped by the joined invoice talbes column contractID which is why there was columns missing.

renegade999
  • 84
  • 8