Determine if Linking ID is Set

[rrstudio2@icqmail.com]

Let say I have a data table that list a name, ID for the last name, a
field linking it to another table, and a field listing if an optional
LinkStatus. I want to run a check to determine if each last name ID
has a Link to the external ID and exclude ones that have a linkstatus
value. Example:

Name ID LinkedToExtID LinkStatus
Smith, J 1 A24
Smith, H 1
Henry, B 2
Franks, D 3
Franks, E 3 A47
Franks, F 3
Rose, J 4
Rose, L 4
Rose, S 4
Jones, A 5 InProgress
Jones, H 5

Want to run a query returning
ID
2
4
because none of these IDs have a corresponding LinkedToExtID value or
any value in the LinkStatus field. IDs 1 and 3 have a LinkedToExtId
value and ID 5 has a status, so 2 and 4 are the only problems. After
several hours of trying things, I can't seem to get this one.

Any thoughts,
Andrew V. Romero

[Tom van Stiphout]

Re: Determine if Linking ID is Set

On Wed, 30 Apr 2008 15:23:02 -0700 (PDT), "rrstudio2@icqm ail.com"
<rrstudio2@icqm ail.comwrote:

If I understand correctly, you need:
select distinct ID from SomeTable
where LinkedToExtID is null and LinkStatus is null

-Tom.

>Let say I have a data table that list a name, ID for the last name, a
>field linking it to another table, and a field listing if an optional
>LinkStatus. I want to run a check to determine if each last name ID
>has a Link to the external ID and exclude ones that have a linkstatus
>value. Example:
>
>Name ID LinkedToExtID LinkStatus
>Smith, J 1 A24
>Smith, H 1
>Henry, B 2
>Franks, D 3
>Franks, E 3 A47
>Franks, F 3
>Rose, J 4
>Rose, L 4
>Rose, S 4
>Jones, A 5 InProgress
>Jones, H 5
>
>Want to run a query returning
>ID
>2
>4
>because none of these IDs have a corresponding LinkedToExtID value or
>any value in the LinkStatus field. IDs 1 and 3 have a LinkedToExtId
>value and ID 5 has a status, so 2 and 4 are the only problems. After
>several hours of trying things, I can't seem to get this one.
>
>Any thoughts,
Andrew V. Romero

[rrstudio2@icqmail.com]

Re: Determine if Linking ID is Set

On Apr 30, 9:17 pm, Tom van Stiphout <no.spam.tom7.. .@cox.netwrote:

On Wed, 30 Apr 2008 15:23:02 -0700 (PDT), "rrstud...@icqm ail.com"
>
<rrstud...@icqm ail.comwrote:
>
If I understand correctly, you need:
select distinct ID from SomeTable
  where LinkedToExtID is null and LinkStatus is null
>

Won't select distinct ID where the LinkedToExtId and LinkStatus is
null also return values like ID 3 since there are 2 rows with the ID
of 3 also have null LinkedToExtID and null Linkstatus fields? Since
there is one row with an ID of 3 that has a value in the
LinkedToExtID, I want to exclude it. Only looking for IDs that don't
have a value in the LinkedToExtID or LinkStatus in any of their
records.

Thanks,
Andrew V. Romero