Query Object in NAV - SQL "WHERE ... IS NULL"

norseny

Hi,

I have a huge amount of records in one table, the structure and sample data is more less like this:

ID | Name | Related ID
1 | Name1 | 2
3 | Name3 | 1
4 | Name4 | 3
5 | Name5 | 1
6 | Name6 | 2
etc.

I need to find only those records whose Related ID exists as ID in this table. So from the records above, the query should return 3, 4, 5. 1 and 6 should be excluded, because there is no record with ID = 2.

---
Edit (I noticed now the logical mistake in my description in sentence above):
I need to find only those records whose Related IDs do not exist as ID in this table. So from the records above, the query should return 1 and 6, because there is no record with ID = 2. Records 3, 4, 5 should be excluded.
---

In SQL it is quite simple:

SELECT t1.Name
FROM table1 t1
LEFT JOIN table1 t2 ON t1."Related ID" = t2.ID
WHERE t2.ID IS NULL

Is there a way to do it efficiently in NAV (maybe using query object?).
I am trying to avoid "IF NOT Table.GET("Related ID") THEN..." for every record, it would generate so many queries from NAV to DB.

vremeni4

I think you can do this with one query , by using the Property DataItemLinkType and option
SQL Advanced Options
Then In SQLJoinType you can select "Inner Join"
I would use then the totals=1 to get the correct entries.

I hope this helps.

norseny

@vremeni4, thank you for the response!
I however noticed that I made a mistake in my description above, I already edited it.

So my task is the opposite of what you, @vremeni4, suggested. Have you got any idea how to solve this?

For now I just loop through all the records and check each one with
"IF NOT Table.GET("Related ID") THEN..."

lubost

NAV doesn't use NULL. Each NAV table must not use NULL. All columns in NAV table are filled with default value ('' for text, 1.1.1753 for date etc.).

norseny

@lubost, thanks for the answer.
I know, but it has nothing to do with what I ask about.
The SQL condition WHERE t2.ID IS NULL in my case just helps finding the records that in "Related ID" field have the value that does not exist in the same table as "ID".