1

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.

Improve this question
8
  • If the solution is resolved please change it to resolved. So others can get help of it. @user2173803 Commented Apr 16, 2014 at 15:16
  • @HarishChinju your answer is very simple, And its NOT correct. Please 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. Commented Apr 16, 2014 at 15:29
  • Your example data is incomplete. I'm not sure if you want only the topmost node in the tree or every node in the tree that is a parent. And do you want the topmost node only if NO parents in the tree have recycle_bin = 1 or do you only care about the topmost node? Also - can the topmost node be determined merely by the fact that it has a parent_id = 0? Or does one need to traverse the whole tree for each row to get to the topmost node for creater_id = 7923? Commented Apr 16, 2014 at 15:38
  • theres a problem with your logic... if you check the second time for a parent.. when your project_id is 100 then you will get the first row because they both are 0. 0 can be an ID so you need to clarify that the 0 means there is no parent_id Commented Apr 16, 2014 at 16:00
  • also if you want it to go through a tree then give an example that has a tree... as in we need more than just two layers. Commented Apr 16, 2014 at 16:01

3 Answers 3

Reset to default
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;
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

ok i just updated.. let me know if that solves it for all of the data
my updated sql is printing out your expected output.. screencast.com/t/FEU1ngand... is that not what you want? -- i updated the NEW_FIDDLE link to the one that prints it out correctly
let me see what I can do
also you forgot to add a condition ..creater_id=7923 , if this condition is not there then it will show other users data too.. i.e why i remind you..
i figured that that was a simple add on ( on your side) since the data is all 7923 on my side its just less checking.. so it performs faster in the sql fiddle. I'll add it
|
1

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;
Improve this answer

6 Comments

why a subquery instead of recycle_bin!=1
your answer is not correct, its not simple as this, may be you could not get what i am looking for...
What can try to achieve it. What is your exact need.?
from the above records i want only those rows, whose parent_id does not have 'recycle_bin` value as 1 and also should display record for creater_id=7923 ;
@HarishChinju You can see my new records, & expected output above, please help if you can
|
1

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.

Improve this answer

8 Comments

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.
So no parent in the whole tree structure can have a recycle_bin = 1. Is that correct?
I updated the answer based on your clarifications. I believe this will satisfy the question as I currently understand it.
I don't believe this will work.. because if im not mistaken you don't have access to the PARENT outside of the scope of the NOT EXISTS()... to get this to work you can flip it around... change PARENT to CURRENT_ROW in the outer select.. and then change PARENT.parent_id to CURRENT_ROW.parent_id, and CURRENT_ROW.project_id to PARENT.project_id
i have update my expected output above with new records, that is 100% correct, record. How to get that expected output... i tried your above code but it did not appear to be right..
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.