Thread: SQL Question
I have a table called ‘tasks’ with a structure like this:
Taskid ParentId
----------------------------
1 null
2 1
3 1
4 Null
5 4
6 4
7 Null
8 Null
9 15
10 18
The taskid column is the primary key and the parentid column references values in the taskid column. However, it is possible for the parentid to be a value that is not found in the taskid column (hence my question…). I need to write a query that will retrieve all rows where the parentid is either ‘null’ or does not exist in the taskid column.
Any help would really be appreciated.
Thanks!
Kevin
On 2/22/06 9:43 AM, "Kevin Crenshaw" <kcrenshaw@viscient.com> wrote: > I have a table called 'tasks' with a structure like this: > > Taskid ParentId > ---------------------------- > 1 null > 2 1 > 3 1 > 4 Null > 5 4 > 6 4 > 7 Null > 8 Null > 9 15 > 10 18 > > > The taskid column is the primary key and the parentid column references > values in the taskid column. However, it is possible for the parentid to be > a value that is not found in the taskid column (hence my question.). I need > to write a query that will retrieve all rows where the parentid is either > 'null' or does not exist in the taskid column. How about: Select * from tasks where parentid is null or parentid not in (select distinct(taskid) from tasks);
That did the trick. Thanks for your help! -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Sean Davis Sent: Wednesday, February 22, 2006 10:03 AM To: Kevin Crenshaw; pgsql-novice@postgresql.org Subject: Re: [NOVICE] SQL Question On 2/22/06 9:43 AM, "Kevin Crenshaw" <kcrenshaw@viscient.com> wrote: > I have a table called 'tasks' with a structure like this: > > Taskid ParentId > ---------------------------- > 1 null > 2 1 > 3 1 > 4 Null > 5 4 > 6 4 > 7 Null > 8 Null > 9 15 > 10 18 > > > The taskid column is the primary key and the parentid column references > values in the taskid column. However, it is possible for the parentid to be > a value that is not found in the taskid column (hence my question.). I need > to write a query that will retrieve all rows where the parentid is either > 'null' or does not exist in the taskid column. How about: Select * from tasks where parentid is null or parentid not in (select distinct(taskid) from tasks); ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster