473,748 Members | 10,737 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Aug 28 '06 #1
5 5066
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_Ancesto r = 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.

Aug 28 '06 #2
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

Sep 1 '06 #3
Thanks very much! All went well, and the Orphans are history.

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

Sep 4 '06 #4
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_Ancesto r = 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
Sep 23 '06 #5
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_Ancesto r = 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_Ancesto r = 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
Sep 25 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
7393
by: Jack | last post by:
I have a text file of data in a file (add2db.txt) where the entries are already entered on separate lines in the following form: INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great atmosphere. Good food.", " (Harry Houdini - 03/01/2004)"); INSERT INTO `reviews` VALUES("", "Le Chow Place", "Lunch", "yada yada", " (Herbert Hoover - 03/03/2004)"); INSERT INTO `reviews` VALUES("", "Golden Dragon", "Lunch", "Exquisite.
0
6459
by: Gordon | last post by:
I have 2 tables t and t1. In this case, t1 is a copy of t. I want to delete rows from t1 based on criteria on the t table and a relationship between t ad t1 (in this case the id column). In the results below, I would think that the delete should have deleted row 1 {1 5 me) and not row 3 (1 5 they) when I run this statement delete t1 from t, t1 where t.id = t1.id and t.id=1 and t.name = 'me'; Any ideas on why row 2 is deleted?
0
5000
by: Fatt Shin | last post by:
Hi, I'm running MySQL 4.0.13, connecting from PowerBuilder 9 using ODCB Connector 3.51. I'm facing a problem where whenever I issue a SELECT COUNT(*) statement from PowerBuilder, I always get SQL syntax error back from MySQL. (Refer to ODBC Trace I captured below). metrohouse af8-b94 ENTER SQLExecDirect HSTMT 014D2360 UCHAR * 0x020A0EA2 "select count ( *) from code
0
3069
by: B. Fongo | last post by:
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me solve my problem. I'll turn to MySQL doc after getting through this pressing project. Thanks a lot Roger! Babale -----Urspr=FCngliche Nachricht-----
0
2691
by: Philip Stoev | last post by:
Hi all, Please tell me if any of this makes sense. Any pointers to relevant projects/articles will be much appreciated. Philip Stoev http://www.stoev.org/pivot/manifest.htm ===================================
2
2606
by: Beyonder | last post by:
I have five tables in my database, there are actually NO common fields between them, not even a KEY or ID or anything like that, except for the "body" of a blob field. and that text is not identical, just a portion of that text is identical. each table has 5 fields, all different except the blob, which is called "message", so normally I use something like: select * from table1 where message like '%apple%';
2
2309
by: JellyMan | last post by:
I have two tables in my Access database that I want to be able to synchronize by pressing a form button. I think it can work, but I am not sure how. I am trying to use a single query to do this. The first table, and more updated table, is called tbl_hours_new. This table is a linked table to an ODBC conduit to a MySQL database on the web. (I have full access to this database). The second table is called tbl_hours_Palm1. This table has...
1
2526
by: paulq182 | last post by:
PLEASE HELP ME WITH MY CODE?? import java.sql.*; import java.io.*; class min_filmdb_rel_mysql { public static void main (String args ) throws SQLException, IOException {
0
2255
by: Zettai | last post by:
Hi All, I am trying to query a mysql database using the 'LIKE' keyword within my query based on what the user has input. It works if I pass the string, however I am having trouble getting it to expand the parameter when combining it with the LIKE MySQL keyword. As seen below parameters work in a simple SELECT without 'LIKE': string sql2 = "SELECT * FROM vocab WHERE english LIKE '%" + english + "%'"; string sql3 = "SELECT * FROM...
0
8991
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9370
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8242
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6796
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4602
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4874
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3312
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2782
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2215
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.