CODEWITHSUNDEEP
phpmysqlsqldatabase
Abhishek Agarwal
Abhishek Agarwal

Reputation: 724

MySQL retrieve no of users a user has of user resultset

I have two tables users and parents.

  1. Each user has a unique id and a parent id(this is one who referred him to website).
  2. Parent id of root user is 0.
  3. Users that have same parent id are childrens of that id

users
id
name
email

parents
id
parentId
joiningAmount

i want a query in which i will give id of a parent.

This query should return all of its children with following information

id, name, email, joiningAmount, (No of Childrens this user has)

Is it possible to do this in one query?
If not how should i normalize database in order to complete this in one query.
Any help is highly appreciated.

EDIT:

The users table will contain all the user information

Suppose this is users table.

id  name    email
1   Admin   [email protected]
2   John    [email protected]
3   Larry   [email protected]
4   Jone    [email protected]

Registration is possible only by referral.
As admin is not referred by anyone his parentId is 0.
Suppose admin refers larry and john(thus their parent ids are 1) and john bring another user from his referral whose name his jone(thus his parent id is that of john) then parents table should look like this.

Joining amount can be ignored for now as its just part of application.

id  parentId  joinAmount
1   0        1000
2   1        1000
3   1        1000
4   2        1000

so now i want a query to which if i pass an id of 1(admins id) it should return his children's

id, name, email, joiningAmount, (**No of Childrens this user has**)

2, John, [email protected], 1000, 1(as john has 1 child)
3, Larry, [email protected], 1000, 0

Upvotes: 0

Views: 112

Answers (3)

Keith Irwin
Keith Irwin

Reputation: 5668

Assuming that you don't change the tables the way suggested (which you probably should) then the appropriate query would be:

SELECT users.id, users.name, users.email, parents.joiningAmount,
    (SELECT COUNT(*) FROM parents P WHERE P.parentId=users.id) 
FROM users,parents
WHERE users.id=parents.id;

Upvotes: 0

KalenGi
KalenGi

Reputation: 2077

Building on the answer by Unamata Sanatarai, here's the query:

SELECT users.id, users.name, users.email, users.joiningAmount, 
(SELECT COUNT(*) FROM users AS children WHERE children.parent_id = users.id ) AS children_count 
FROM users WHERE users.parent_id = 3

Upvotes: 1

Unamata Sanatarai
Unamata Sanatarai

Reputation: 6637

I suggest placing it into just one database like so

users
- id
- parent_id
- name
- email
- joiningAmount

That way you have only one database and you retrieve everything with a query like:

SELECT id, name, email, joiningAmount FROM users WHERE parent_id = 3

Upvotes: 0

Related Questions