joyce — Fri, 30 Nov 2001 07:29:28 +0000

i was told that mysql canot have sub queries. so i separate the queries into 3.

first, i execute the first query:

$category_ID = 7

SELECT category_ID 
FROM category_review 
WHERE category_ID = $category_ID 
OR  category_parent = $category_ID 

i got 2 records, which is "7 and 8"

and then i execute the second query:

SELECT category_parent 
FROM category_review 
WHERE category_parent = $category_ID 
OR category_ID = category_parent

and i got the result as "7".

after that i execute the third query:

DELETE FROM category_review WHERE category_ID IN (7,8)

2 records were deleted. which is :
category_ID = 7, and category_ID=8
i have 4 levels for this tree and this query only delete 2 levels from my folder tree. my third and fourth level are not deleted.

pls help!

Mark Hensler — Fri, 30 Nov 2001 06:27:47 +0000

I think that those ANDs need to be ORs...

DELETE FROM category_review 
WHERE category_ID IN 
  (SELECT category_ID 
    FROM category_review 
    WHERE category_ID = $category_ID 
    OR  category_parent = $category_ID 
  UNION 
    SELECT category_parent 
    FROM category_review 
    WHERE category_parent = $category_ID 
    OR category_ID = category_parent) 

'Is that easier to read?

The UNION takes the results of two SELECT queries and appends the one under the under. Then the DELETE query is looking for category_ID IN the unified result of the two select queries.

Basically, your querying for all records WHERE category_ID=$category_ID OR category_parent=$category_ID. Then your deleting them all.

mySQL Docs:
6.4.6 DELETE Syntax
6.4.1 SELECT Syntax
1.7.4.1 Sub-SELECTs

joyce — Fri, 30 Nov 2001 03:34:57 +0000

hmm...i'm kind of confuse here...

Quote:
DELETE FROM category_review
WHERE category_ID IN
(SELECT category_ID
FROM category_review
WHERE category_ID = $category_ID
AND category_parent = $category_ID
UNION
SELECT category_parent
FROM category_review
WHERE category_parent = $category_ID
AND category_ID = category_parent)

why do i need to select category_ID while i already know category_ID=$category_ID?? the same goes to category_parent..

i heard that mysql doesn't support sub queries....

i also got an error when i tried to execute this part:

SELECT category_ID 
FROM category_review 
WHERE category_ID = $category_ID 
AND category_parent = $category_ID 
UNION 
SELECT category_parent 
FROM category_review 
WHERE category_parent = $category_ID 
AND category_ID = category_parent

joyce — Fri, 30 Nov 2001 02:26:01 +0000

i keep having this error when i tried to execute the query:

You have an error in your SQL syntax near 'SELECT category_ID 
FROM category_review 
WHERE category_ID = 2
AND category_' at line 3

wat is wrong??

Peter J. Boettcher — Thu, 29 Nov 2001 14:54:44 +0000

Try this:

DELETE FROM category_review
WHERE category_ID IN
(SELECT category_ID
FROM category_review
WHERE category_ID = $category_ID
AND category_parent = $category_ID
UNION
SELECT category_parent
FROM category_review
WHERE category_parent = $category_ID
AND category_ID = category_parent)

joyce — Thu, 29 Nov 2001 09:19:43 +0000

anybody?? help help..

guess my question is not clear enough.

for eg.
3 levels in a folder tree, 3 levels also means 3 categories.

1. Book
    1.1. PHP 
        1.1.1. PHP Programming for Beginners
        1.1.2. PHP Programming for Professionals
                  1.1.2.1 First Version
                  1.1.2.2 Second Version
    1.2. ASP
        1.1.1. ASP Programming for Beginners
        1.1.2. ASP Programming for Professionals

if i delete 1.1PHP, means the levels under PHP hv to be deleted too. how am i suppose to generate the sql query to delete?

my table structure is like this:

___________________________________________________
category_ID | category_name | category_parent | category_flag
___________________________________________________

        1         |         Books          |               0          |            1
----------------------------------------------------------------------------------
        2          |        PHP             |             1            |            1
---------------------------------------------------------------------------------
        3          |     PHP P F Beg    |              2           |           1
----------------------------------------------------------------------------------
        4          |      PHP P F Pro    |             2            |            1
----------------------------------------------------------------------------------
        5          |      First Version   |             4            |            1
----------------------------------------------------------------------------------
        6          |  Second Version  |             4             |           1
----------------------------------------------------------------------------------
        7          |        ASP              |             1             |            1
----------------------------------------------------------------------------------
        8          |     ASP P F Beg     |             7             |            1
----------------------------------------------------------------------------------
        9          |     ASP P F Pro     |              7            |             1
----------------------------------------------------------------------------------

joyce — Thu, 29 Nov 2001 02:47:57 +0000

well, levels means categories.....

for eg..i have 5 levels, means i have a root category, then i have sub category, and then sub sub category, and then sub sub sub category, and then sub sub sub sub category..

Peter J. Boettcher — Wed, 28 Nov 2001 14:18:30 +0000

I'm not sure what you mean. When you say levels, do you mean categories?