# Thread: St*p1d Problem with a query

1. Registered User
Join Date
Sep 2003
Posts
7

## Unanswered: St*p1d Problem with a query

I really don`t know how to do this. Can anyone light me up a little?

I have a table with this ( most significant things )

- id ( )
- idfather ( link to another id )
- active
- confirm

I have to SELECT all the items in this table that DOESN'T have any child item or childs with active=1 or confirm=0, that have active=0 and confirm=1 itself.

This table is LOOK by a TREE so the rows are link to another with idfather.

Can anyone give me a clue? or ideas?

Thx

2. The SQL Apostle
Join Date
Jul 2003
Location
The Dark Planet
Posts
1,401
select * from table where fatherid not in (select id from table where active = 1 or confirm = 0) and active=0 and confirm=1

3. Registered User
Join Date
Sep 2003
Posts
7
Another question, is much faster joins against IN?

Cuz I try IN and is very slow because the SubQuery is done EVERY row...

that's correct?

4. Registered User
Join Date
Sep 2003
Posts
7
Originally posted by Enigma
select * from table where fatherid not in (select id from table where active = 1 or confirm = 0) and active=0 and confirm=1
No, this query is not what I mean to need.

I need that both tables are linked... something like:

SELECT t1.Id FROM table t1 JOIN table t2 ON t1.Id=t2.Idfather WHERE (t1.active=0 and t1.bconfirm=1) and (t2.active=1 or t2.confirm=0)

5. The SQL Apostle
Join Date
Jul 2003
Location
The Dark Planet
Posts
1,401
Originally posted by Oaky
No, this query is not what I mean to need.

I need that both tables are linked... something like:

SELECT t1.Id FROM table t1 JOIN table t2 ON t1.Id=t2.Idfather WHERE (t1.active=0 and t1.bconfirm=1) and (t2.active=1 or t2.confirm=0)
Sorry ... wouldnt be able to help right now .. am nowhere near a sql box ...
maybe brett can cook up something

6. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
This will show you (with you're criteria...which I might add could foul things up)

A parent with no children...

Code:
SELECT   t2.Idfather
FROM   table t2
LEFT JOIN   table t1
ON   t1.Id=t2.Idfather
WHERE ( t1.active=0 AND t1.bconfirm=1 )
AND ( t2.active=1 OR  t2.confirm=0 )
AND   t1.id IS NULL

7. Registered User
Join Date
Mar 2004
Posts
45
Try this:

SELECT ID
FROM Table p
WHERE Active = 0 AND Confirm = 1
AND NOT EXISTS
(SELECT *
FROM Table
WHERE IDFather = p.ID
AND Active = 1 OR Confirm = 0)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•