Find missing level number in dable.

[EM1107]

Good day Everyone.

I have a table that include sequence value from different tables.
I have created a query that does show me all the values for all entries in the table and I have query showing me individual entries if I know the entry does have a broken value. I would like to create a query that I can run and only return all the users with broken entries without having to go through them all one at the time.

Here is a example.

USER LEVEL SECTION
501 0 File
501 1 Administration
501 2 General
501 3 Management
501 4 Archives
501 5 Level 1
501 6 Date

Would anyone have an idea as to how I can query the tables to only return the users with a broken Level value?

 

[bborissov]

OK,
What do you want from this example?

Borislav Borissov
VFP9 SP2, SQL Server

 

[EM1107]

Sorry If I was not clear. What I want is that in some case these values from 0 to 6 may have missing levels for user value 501. So I would like to be able to display the values that have levels values.

My example shows a good value but there could be bad values also but my query will show me all entries not only the one that has bad values.

Here is a new example.

If you assume that I have run my query and it return the following.

USER LEVEL SECTION
501 0 File
501 1 Administration
501 2 General
501 3 Management
501 4 Archives
501 5 Level 1
501 6 Date
502 0 File
502 1 Administration
502 2 General
502 4 Archives
502 5 Level 1
502 6 Date

All I want to see is the entry with the missing entry so what display bellow:

USER LEVEL SECTION
502 0 File
502 1 Administration
502 2 General
502 4 Archives
502 5 Level 1
502 6 Date

 

[bborissov]

Do you have table with LEVEL SECTIONs?

Borislav Borissov
VFP9 SP2, SQL Server

 

[EM1107]

It is actually 3 tables I have here.
The user come from a table that is link to the level table and the section table is also linked to the level table.
So The value on the level determine how many level there is for that entry. The value can vary from user to user. A user could have one and an other user could have 4 or even 8 level.

The level column act as a sequence number but must always a full sequence. from 0 to what ever the number is the last number but no hole in between. What I want to find is the entries with a hole in them.

 

[Andrzejek]

So in other words, you want to see this:

[pre]
USER LEVEL SECTION
502 0 File
502 1 Administration
502 2 General
502 4 Archives
502 5 Level 1
502 6 Date
[/pre]
Because user 502 does NOT have LEVEL 3 / SECTION Management record.


---- Andy

There is a great need for a sarcasm font.

 

[EM1107]

That is absolutely correct.

 

[Andrzejek]

Question: if you have records for user 505:

[pre]
USER LEVEL SECTION
505 0 File
505 1 Administration
505 2 General
505 3 Management
505 4 Archives
[/pre]
How would you know that this record is missing if user 505 should have Level 5

[pre]
USER LEVEL SECTION
505 5 Level 1[/pre]

Do you have a table where all users are mentioned with all the levels they should have?
And do all users have to have LEVEL 0 - File ?


---- Andy

There is a great need for a sarcasm font.