473,699 Members | 2,842 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 5061
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
7385
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
6457
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
4993
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
3068
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
2602
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
2304
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
2523
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
2253
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
9174
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8883
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7750
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
6534
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
5874
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4376
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
4629
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2347
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2009
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.