Easy Transact-SQL code being difficult

"tt" <thiest@ayresassociates.com> wrote:
>
>Hello,
>Ive got a table that tracks folders and each folder has a parent folder
>except for the root folder whose parent field is null.
>
>The tables name is Folders. Folders fields:
>ID (int, Not Null),
>Name (varchar, Not Null),
>ParentFolderID (int, Not Null),
>
>All pretty basic.
>Due to some interesting problems elsewhere, it became important to find
all
>folders that dont contain folders within them (all folders that are not
>Parent Folders to some other folder). We know that there are such folders
>because our database is finite and Ive seen them. So, I wrote the following
>code:
>
>SELECT I.ID, I.ParentFolderID
>FROM Folders as I
>WHERE I.ID not in (SELECT F.ParentFolderID FROM Folders as F)
>ORDER by I.ID
>
>My result set is empty, not a one shows up.
>Why?
>
>Todd

Kevin, thanks. First off, you're right, I mistyped. The parentID field does
allow nulls. The ID field doesn't.
And the code works like a charm. I'd still love to figure out why the other
code doesn't work. Curiosity killed that cat, I guess.
Thanks again,
Todd

"KevinV" <kevjver@hotmail.com> wrote:
>
>Sorry about the last non-response, my fingers aren't doing what I tell them
>to this morning.
>
>Could the problem be related to nulls? You said the columns are defined
as
>not null but you also said the root folder has a null parent field. I'm
not
>sure how this may have an effect but it was the only thing I could think
>of.
>
>Could I suggest changing the query to something like;
>
>SELECT I.ID, I.ParentFolderID
>FROM Folders as I
>WHERE I.ID not exists
>(SELECT * FROM Folders as F where F.ParentFolderID = I.ID)
>ORDER by I.ID
>
>It may work better and be more efficient.
>
>Kevin
>
>"KevinV" <kevjver@hotmail.com> wrote:
>>
>>"tt" <thiest@ayresassociates.com> wrote:
>>>
>>>Hello,
>>>Ive got a table that tracks folders and each folder has a parent folder
>>>except for the root folder whose parent field is null.
>>>
>>>The tables name is Folders. Folders fields:
>>>ID (int, Not Null),
>>>Name (varchar, Not Null),
>>>ParentFolderID (int, Not Null),
>>>
>>>All pretty basic.
>>>Due to some interesting problems elsewhere, it became important to find
>>all
>>>folders that dont contain folders within them (all folders that are not
>>>Parent Folders to some other folder). We know that there are such folders
>>>because our database is finite and Ive seen them. So, I wrote the following
>>>code:
>>>
>>>SELECT I.ID, I.ParentFolderID
>>>FROM Folders as I
>>>WHERE I.ID not in (SELECT F.ParentFolderID FROM Folders as F)
>>>ORDER by I.ID
>>>
>>>My result set is empty, not a one shows up.
>>>Why?
>>>
>>>Todd
>>
>

The important thing I noticed is that you stated "Ive got a table that tracks
folders and each folder has a parent folder except for the root folder
whose parent field is null."

Doesn't that mean that you just need to do a simple select statement from
you Folders table where ParentID IS NULL?? What am I missing?

Kevin G. Boles
President
Indicium Resources, Inc.

I think you forgot to correlate your sub-query:

SELECT i.*
FROM folders i
WHERE id NOT IN (
SELECT id
FROM folders f
WHERE f.parentfolder = i.id <-- magic bit!
)

Without the WHERE clause the sub-query returns every folder, so no rows from
the outer query match the NOT IN condition (every folder is in the set of
every folder).

HTH
Simon

"tt" <thiest@ayresassociates.com> wrote:
>
>Hello,
>Ive got a table that tracks folders and each folder has a parent folder
>except for the root folder whose parent field is null.
>
>The tables name is Folders. Folders fields:
>ID (int, Not Null),
>Name (varchar, Not Null),
>ParentFolderID (int, Not Null),
>
>All pretty basic.
>Due to some interesting problems elsewhere, it became important to find
all
>folders that dont contain folders within them (all folders that are not
>Parent Folders to some other folder). We know that there are such folders
>because our database is finite and Ive seen them. So, I wrote the following
>code:
>
>SELECT I.ID, I.ParentFolderID
>FROM Folders as I
>WHERE I.ID not in (SELECT F.ParentFolderID FROM Folders as F)
>ORDER by I.ID
>
>My result set is empty, not a one shows up.
>Why?
>
>Todd