Results 1 to 7 of 7
  1. #1
    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. #2
    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
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    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. #4
    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. #5
    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
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    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
  •