Hi,
i have the following problem.
I have a table containing contracts: "CONTRACTS", a table "PERSON" is connected through ID_P.
CONTRACT:
| ID_V | ID_P | PRODUCT | VALUE |
| 1 | P1 | PRODUCT 1 | 100 |
| 2 | P1 | PRODUCT 2 | 200 |
| 3 | P2 | PRODUCT 3 | 300 |
| 4 | P2 | PRODUCT 4 | 400 |
| 5 | P4 | PRODUCT 5 | 350 |
| 6 | | PRODUCT_ALL | 500 |
PERSON:
| ID_P | NAME | PERSGROUP |
| P1 | PERSON 1 | 111 |
| P2 | PERSON 2 | 111 |
| P3 | PERSON 3 | 222 |
| P4 | PERSON 4 | 999 |
As a result i have 1 contract (ID_V = 6) with no PERSON.
Now I want to receive all Contracts with Persons without 999 but included NULL.
I tried to search in PERSGROUP for -999 but NULL disappears.
I receive the right amount if I search in ID_V for "=COUNT(IF(PERSGROUP <> '999',ID_V))". In my opinion: "no business user will be able to create this kind of search filter".
I could also add "null"-PERSON to PERSON Table and x-more tables with possible "null"-Dimensions. But is there a more user friendly and less data modell change demanding solution? Search in PERSGROUP like "(-999|ISNULL) or something like that?
I really appreciate your answer.
Konstantin
