SQL statement to get all the users

Question

I have this table and I am confuse how can I generate sql statement on this on how can I get ALL the u_id,that under p_id as there parent id.

here is the output I want if want only 5 as the p_id.

users       usernames       p_id
7           miller           5
8           jamie            5
9           honey            7
10          jack             7

gather_table

p_id        u_id

null         1
1            5
1            6
5            7
5            8
7            9
7            10

user table

user_id      user_name

1            millan
2            john
3            max
4            chris
5            jane
6            lester
7            miller
8            jamie
9            Honey
10           Jack

Thank you in advance.

EDIT:

I tried >=6 but it returns 9 and 10,but 6 is never been p_id in my gather table so it has no u_id's on my gather table.

I want to get all the u_id of my p_id which is belong to it(p_id). in my example 5 is p_id of 7 8 9 10, eventhough the p_id of 9 and 10 is 7 but the p_id of 7 is 5,so they are still under 5

Do you know about [joins](https://en.wikipedia.org/wiki/Join_(SQL))? — icktoofay, Dec 10 '14 at 05:32

score 2 · Answer 1 · edited May 23 '17 at 12:27

2

You can use left join for this purpose

SELECT `g`.`p_id`, `u`.* FROM `gather_table` as `g` LEFT JOIN `user_table` as `u` ON `g`.`u_id` = `u`.`user_id` WHERE `g`.`p_id` >= 5

UPDATE :

You can check the answer of Finding all parents in mysql table with single query (Recursive Query)

But if there is only one parent each u_id, then I would suggest you to use Nested Set Table structure, which is more elegant way to handle this type of problem. In this the table structure would be like

CREATE TABLE IF NOT EXISTS `user_table` (
  `u_id` int(11) NOT NULL AUTO_INCREMENT,
  `p_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `lft` int(11) NOT NULL DEFAULT '0',
  `rgt` int(11) NOT NULL DEFAULT '0',
  `level` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`u_id`),
  KEY `idx_left_right` (`lft`,`rgt`)
) DEFAULT CHARSET=utf8;

You can take reference from this article. This is specific to Joomla, but probably will be helpful.

edited May 23 '17 at 12:27

Community

1
1

answered Dec 10 '14 at 05:32

Gaurav

28,447
8
50
80

I have something to say, what if the >= 6 but 6 has no u_id in my gather_table – ashTon Dec 10 '14 at 06:47
Then it should return empty results. – Gaurav Dec 10 '14 at 07:48
Okay. Yes, my mistake. It will return the record of 9, 10. Which is correct according to your scenario. What do you want in that case? – Gaurav Dec 10 '14 at 08:06
Do I need to change my user table ? the one that you posted ? – ashTon Dec 10 '14 at 09:22
I was confuse on how to insert the level – ashTon Dec 10 '14 at 18:08
You can take reference from this code http://hg.joomla.org/joomla-platform/src/247ba8d88526/libraries/joomla/database/tablenested.php – Gaurav Dec 11 '14 at 19:35

score 1 · Answer 2 · answered Dec 10 '14 at 05:32

1

select t1.user_id ,t1.user_name,t2.p_id from user as t1 
inner join gather as t2 on t1.user_id = t2.u_id
where t2.p_id >= 5

Your desired output shows you want p_id >= 5. Try join query for this Check the documentation for more

answered Dec 10 '14 at 05:32

웃웃웃웃웃

11,829
15
59
91

what if >=6 ? but 6 never been p_id in my gather_table it has no u_id,but i tried your solution it return 9 and 10 – ashTon Dec 10 '14 at 08:02
Yes it will return 9,10 if you tried with >= 6 because 7 is the only p_id which is >= 6.So what is wrong with that.? – 웃웃웃웃웃 Dec 10 '14 at 08:04

Suchit kumar · Answer 3 · 2014-12-10T05:34:47.910

1

use join on your table:

SELECT gather_table.p_id, user_table.* FROM gather_table  LEFT JOIN user_table ON gather_table.u_id = user_table.user_id WHERE gather_table.p_id = 5

edited Dec 10 '14 at 05:34

answered Dec 10 '14 at 05:34

Suchit kumar

11,809
3
22
44

score 0 · Answer 4 · answered Dec 10 '14 at 06:01

0

Hope this will help you

select g.u_id,g.p_id,u.user_name from gather_table g,user_table u where g.u_id=u.user_id and g.u_id>=7

answered Dec 10 '14 at 06:01

Vamshi .goli

522
4
13

SQL statement to get all the users

4 Answers4