Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

doing a search based on table values in same select

TLDTLD Member Posts: 63
Ok, so the subject line is kinda cryptic, but I'm not really sure how else to explain it, and therefore am not even sure about a search phrase for finding existing documents here.. (= so, if you, once I explain myself, know of a thread that deals with this sort of thing, please provide a link! I'd appreciate it!

Ok, so I've got a table of items, kinda like a linked list. each item has an id, other misc data, and a belongsto value, which is simply an id of the table item who owns said item.

For example, if I have (assumuing 0 is no owner):
id=1, title='The Best Pudding Flavors', belongsto=0
id=2, title='Tapioca', belongsto=1
id=3, title='Banana', belongsto=1
id=4, title='Chocolate', belongsto=1

So, id 2-4 all belong to id 1. Clear so far?

Ok, so, what I want to be able to determine is how can I return a listing of items who's belongsto DOES NOT exist? Meaning, orphaned items. I want to find anything whose belongsto points to an invalid id number.

I can determine a list of unique belongsto's:

SELECT id,belongsto FROM table GROUP BY belongsto;

But, how do I check those values against existing id's? I can't find any EXISTS syntax, except with table creation/modification/deletion.

If this isn't clear, please ask, because this is perplexing! (=

Thanks!!

TLD
TheLinuxDuck
"Truely you have a dizzying intellect" - The Princess Bride

Comments

  • adamAadamA Member Posts: 2
    Maybe I misunderstand, but couldn't you just do a query of the owners table ids, and then use double while loops to check each pudding id against the owner id? I know this'd be really time consuming because of all the calls back to the db, but i'm too lazy to write code to stick the id's in an array (which you can do, I'm sure)

    [code]
    while($pudding_index = mysql_fetch_array($result_pudding_query))
    {
    $check = 0;
    $result_owner_query = mysql_query("SELECT id FROM owners", $db);
    while($owner_index = mysql_fetch_array($result_owner_query))
    {
    if( $pudding_index['belongsto'] == $owner_index['id']) $check = 1;
    }
    if (!$check) APPEND OWNER ID TO STRING/ARRAY/ETC
    }
    [/code]

    I feel bad writing such slow code, but I'm sure you can fix it. Tell me if it doesn't work or something.

    : Ok, so the subject line is kinda cryptic, but I'm not really sure how else to explain it, and therefore am not even sure about a search phrase for finding existing documents here.. (= so, if you, once I explain myself, know of a thread that deals with this sort of thing, please provide a link! I'd appreciate it!
    :
    : Ok, so I've got a table of items, kinda like a linked list. each item has an id, other misc data, and a belongsto value, which is simply an id of the table item who owns said item.
    :
    : For example, if I have (assumuing 0 is no owner):
    : id=1, title='The Best Pudding Flavors', belongsto=0
    : id=2, title='Tapioca', belongsto=1
    : id=3, title='Banana', belongsto=1
    : id=4, title='Chocolate', belongsto=1
    :
    : So, id 2-4 all belong to id 1. Clear so far?
    :
    : Ok, so, what I want to be able to determine is how can I return a listing of items who's belongsto DOES NOT exist? Meaning, orphaned items. I want to find anything whose belongsto points to an invalid id number.
    :
    : I can determine a list of unique belongsto's:
    :
    : SELECT id,belongsto FROM table GROUP BY belongsto;
    :
    : But, how do I check those values against existing id's? I can't find any EXISTS syntax, except with table creation/modification/deletion.
    :
    : If this isn't clear, please ask, because this is perplexing! (=
    :
    : Thanks!!
    :
    : TLD
    : TheLinuxDuck
    : "Truely you have a dizzying intellect" - The Princess Bride
    :

Sign In or Register to comment.