Need correct MySQL SELECT statement

I have a forum table where the field Forum_ID of the first thread
corresponds to itself in the field Forum_Ancestor, and 0 (zero) for the
field Forum_Parent when it is the first topic in a thread:

Example of first topic in thread values
=============================================
Forum_ID=13 (topic)
Forum_Parent=0
Forum_Ancestor=13

If the reply to that topic exists, the Forum_Parent and Forum_Ancestor
are the same:

Example of reply to topic values
=============================================
Forum_ID=24
Forum_Parent=13
Forum_Ancestor=13

If there is a reply to the reply, then the Forum_Parent changes, but
the Forum_Ancestor stays the same:

Example of reply to reply topic values
=============================================
Forum_ID=50
Forum_Parent=14
Forum_Ancestor=13

I accidentally deleted some topic parents and left their children in
limbo and need to find them and delete them also. I have 62,000 records
and don't want to have to select each Forum_Parent value and check it
against every Forum_ID value to see if the parent exists.

How can search for these children in limbo in MySQL? I tried the
following, but it was just a guess and the parents still existed, which
is opposite of what I need:

SELECT *
FROM Forum
WHERE 'SELECT COUNT(*) FROM Forum WHERE Forum_Ancestor=Forum' =0
AND Forum_Parent<>0

Any ideas? As you can probably tell, I am new to MySQL database stuff.

Thanks in advance...

Re: Need correct MySQL SELECT statement

RioRanchoMan wrote:
> I have a forum table where the field Forum_ID of the first thread
> corresponds to itself in the field Forum_Ancestor, and 0 (zero) for the
> field Forum_Parent when it is the first topic in a thread:
>
> Example of first topic in thread values
> =============================================
> Forum_ID=13 (topic)
> Forum_Parent=0
> Forum_Ancestor=13
>
> If the reply to that topic exists, the Forum_Parent and Forum_Ancestor
> are the same:
>
> Example of reply to topic values
> =============================================
> Forum_ID=24
> Forum_Parent=13
> Forum_Ancestor=13
>
> If there is a reply to the reply, then the Forum_Parent changes, but
> the Forum_Ancestor stays the same:
>
> Example of reply to reply topic values
> =============================================
> Forum_ID=50
> Forum_Parent=14
> Forum_Ancestor=13
>
> I accidentally deleted some topic parents and left their children in
> limbo and need to find them and delete them also. I have 62,000 records
> and don't want to have to select each Forum_Parent value and check it
> against every Forum_ID value to see if the parent exists.
>
> How can search for these children in limbo in MySQL? I tried the
> following, but it was just a guess and the parents still existed, which
> is opposite of what I need:
>
> SELECT *
> FROM Forum
> WHERE 'SELECT COUNT(*) FROM Forum WHERE Forum_Ancestor=Forum' =0
> AND Forum_Parent<>0
>
> Any ideas? As you can probably tell, I am new to MySQL database stuff.
>
> Thanks in advance...

Children in limbo are called Orphans, and it's easy to find them with
SQL using an Outer Join with an Is Null condition to limit the results
to records that have no matching parents. Note the recursive Self-Join
with your design since Forum is linked to itself for Replies. Here is
a SELECT that locates the orphans.

SELECT R.Forum_ID AS Orphan, F.Forum_ID As Parent
FROM Forum AS R LEFT JOIN Forum AS F ON R.Forum_Ancestor = F.Forum_ID
WHERE F.Forum_ID Is Null;

R is a table alias for the Reply side of the Forum table while F
indicates the Original subjects. The Orphan column shows their IDs,
while Parent comes up Null.

Re: Need correct MySQL SELECT statement

You e-mailed that it worked.

To delete the orphans, you should be able to just change the SELECT
clause to:

DELETE R.*

Of course, it's helpful to review with SELECT, before issuing DELETE

Re: Need correct MySQL SELECT statement

Thanks very much! All went well, and the Orphans are history.

Best to you, and thanks for your kind help...

Re: Need correct MySQL SELECT statement

Hi,

You helped me with finding Orphans in my database, which worked great!
However, when I finally went to delete them using your suggestion (I
thought I only had to replace the "SELECT" with "DELETE"), I must have
done something wrong. Didn't work as I expected, so could you please
give entire DELETE statement? (Sorry, I am new at this).

Here is the working SELECT statement:
SELECT R. Forum AS Orphan,
F.Forum AS Parent FROM Forum AS R LEFT JOIN Forum AS F ON
R.Forum_Ancestor = F.Forum WHERE F.Forum IS NULL

Thanks in advance,

Z

Skarjune wrote:
> You e-mailed that it worked.
>
> To delete the orphans, you should be able to just change the SELECT
> clause to:
>
> DELETE R.*
>
> Of course, it's helpful to review with SELECT, before issuing DELETE

Re: Need correct MySQL SELECT statement

I still have the Forum tables as in the original thread, and here's the
DELETE I can run:

DELETE R.*
FROM Forum AS R LEFT JOIN Forum AS F ON R.Forum_Ancestor = F.Forum_ID
WHERE F.Forum_ID Is Null;

I notice that your fieldnames have changed...but DELETE R.* in the
first line should work regardless, as long as the SELECT is working.

-David Hedrick Skarjune

RioRanchoMan wrote:
> Hi,
>
> You helped me with finding Orphans in my database, which worked great!
> However, when I finally went to delete them using your suggestion (I
> thought I only had to replace the "SELECT" with "DELETE"), I must have
> done something wrong. Didn't work as I expected, so could you please
> give entire DELETE statement? (Sorry, I am new at this).
>
> Here is the working SELECT statement:
> SELECT R. Forum AS Orphan,
> F.Forum AS Parent FROM Forum AS R LEFT JOIN Forum AS F ON
> R.Forum_Ancestor = F.Forum WHERE F.Forum IS NULL
>
> Thanks in advance,
>
> Z
>
> Skarjune wrote:
> > You e-mailed that it worked.
> >
> > To delete the orphans, you should be able to just change the SELECT
> > clause to:
> >
> > DELETE R.*
> >
> > Of course, it's helpful to review with SELECT, before issuing DELETE