SQL : loop through same table

Question

I have a table "projectdetails" in which parent_id is the foreign key of column project_id in the same table..

From this below records I want only those rows whose parent_id does rows, does not have 'recycle_bin` value as 1 and also should display record for creater_id = 7923 ;

I have record like this:

mysql> SELECT *FROM  projectdetails;
+------------+-----------------+-----------+-------------+------------+
| project_id | project_name    | parent_id | recycle_bin | creater_id |
+------------+-----------------+-----------+-------------+------------+
|          0 | -               |         0 |           0 |       7898 |
|        100 | Country         |         0 |           2 |       7923 |
|        101 | animal          |         0 |           1 |       7923 |
|        102 | India           |       100 |           2 |       7923 |
|        103 | pakistan        |       100 |           2 |       7923 |
|        104 | cow             |       101 |           1 |       7923 |
|        105 | elephant        |       101 |           1 |       7923 |
|        109 | black elephent  |       105 |           1 |       7923 |
|        110 | white elephent  |       105 |           2 |       7923 |
|        111 | wild black elep |       109 |           1 |       7923 |
|        112 | simple blak elp |       109 |           1 |       7923 |
|        113 | lion            |       105 |           1 |       7923 |
|        114 | red lion        |       113 |           1 |       7923 |
|        115 | black lion      |       113 |           1 |       7923 |
|        116 | girls           |         0 |           1 |       7923 |
|        117 | good girls      |       116 |           1 |       7923 |
|        118 | funky girls     |       116 |           1 |       7923 | 
+------------+-----------------+-----------+-------------+------------+
7 rows in set (0.00 sec)

Expected output:

+------------+----------------+-----------+-------------+------------+
| project_id | project_name   | parent_id | recycle_bin | creater_id |
+------------+----------------+-----------+-------------+------------+ 
|        100 | Country        |         0 |           2 |       7923 | 
|        110 | white elephent |       105 |           2 |       7923 |
+------------+----------------+-----------+-------------+------------+

Note: This is tree structure table, here on each child id user can insert many other records.. i.e same like tree structure. So please answer relevant to this note.

If you could not get what i asked for, then please write comment, I will try to explain you...

UPDATE

So basically, when query reads a row to decide whether it is applicable to show or not,

First query flow: say for country.

First look for a row say INDIA, & then next see its parent_id, if it have parent_id, then go to that parent_id(now this is a project_id=100), so next again see whether it has parent or not, if not then see column recycle_bin .. if 1 then show this result or else ignore.

Answer 1

something like this? FIDDLE for reference

SELECT project_id,project_name,parent_id,recycle_bin,creater_id
FROM projectdetails AS pd1
JOIN(
    SELECT parent_id FROM projectdetails as pd
    WHERE pd.parent_id > 0 AND pd.recycle_bin > 1
) AS t ON t.parent_id = pd1.project_id

---

with new data.. NEW_FIDDLE

SELECT 
  if(pd1.recycle_bin >1, pd1.project_id, t.project_id) AS project_id,
  if(pd1.recycle_bin >1, pd1.project_name, t.project_name) AS project_name,
  if(pd1.recycle_bin >1, pd1.parent_id, t.parent_id) AS parent_id,
  if(pd1.recycle_bin >1, pd1.recycle_bin, t.recycle_bin) AS recycle_bin,
  if(pd1.recycle_bin >1, pd1.creater_id, t.creater_id) AS creater_id
FROM projectdetails AS pd1
JOIN(
  SELECT * FROM projectdetails AS pd
  WHERE pd.parent_id > 0 
    AND pd.recycle_bin > 1
) AS t ON t.parent_id = pd1.project_id
GROUP BY project_id

---

without using IF's LAST_FIDDLE

SELECT 
   pd1.project_id,
   pd1.project_name,
   pd1.parent_id,
   pd1.recycle_bin,
   pd1.creater_id
FROM projectdetails AS pd1
WHERE NOT EXISTS(
  SELECT pd.recycle_bin FROM projectdetails as pd
  WHERE pd1.parent_id = pd.project_id 
    AND pd.recycle_bin > 1
) AND pd1.creater_id = 7923 and pd1.recycle_bin > 1
GROUP BY pd1.project_id;

Answer 2

Based on your description, and example output, it looks like you only want to see records with:

1. recycle_bin > 1
2. The topmost node of the parent tree. Topmost node is determined by whether a project with the current node's parent_id exists with the creater_id in question.
3. For creater_id = 7923.

So to get those conditions, you would need three filters:

 SELECT        CURRENT_ROW.PROJECT_ID
              ,CURRENT_ROW.PROJECT_NAME
              ,CURRENT_ROW.PARENT_ID
              ,CURRENT_ROW.RECYCLE_BIN
              ,CURRENT_ROW.CREATER_ID
FROM          PROJECTDETAILS CURRENT_ROW
WHERE         
      recycle_bin != 1
  AND creater_id=7923
  /* This will determine if the node is the topmost based on whether it has a parent */
  AND NOT EXISTS (
         select 1 
         FROM PROJECTDETAILS PARENT 
         where CURRENT_ROW.parent_id = PARENT.project_id
           and PARENT.creater_id = CURRENT_ROW.creater_id
           and PARENT.recycle_bin != 1
          );

See this sqlfiddle for a working example:

This gets the exact output you're looking for.

Answer 3

Try this query,

SELECT 
project_id,project_name,parent_id,recycle_bin,creater_id 
FROM projectdetails
WHERE recycle_bin not in (select recycle_bin from projectdetails where recycle_bin = 1 )
AND creater_id = 7923;

Else try this as @SamD suggested,

SELECT 
project_id,project_name,parent_id,recycle_bin,creater_id 
FROM projectdetails
WHERE recycle_bin != 1
AND creater_id = 7923;