CODEWITHSUNDEEP
mysql
user870130
user870130

Reputation: 575

How to select table names and then count the number of records in each

I have a lookup table called family_names. It contains an id and name. Each name corresponds to a unique table called family_members which has a record for each family member.

For example, family_names might consist of:

table family_names

1 Doe
2 Smith

And then we would have a table named Doe and Smith.

Table doe

1 Wife
2 Child
3 Child

Table smith

1 Husband
2 Child

I want to write a query that selects all the names from family_names. However, I also want it to return the number of family members for each family.

The query should return the following.

[
  {
    "name": "Doe",
    "count": "3"
  },
  {
    "name": "Smith",
    "count": "2"
  }
]

I am having a difficult time approaching this query. What approach might you take? For example, correlated subquery, joins, etc.

Upvotes: 0

Views: 46

Answers (3)

Mahdi Sheikhi
Mahdi Sheikhi

Reputation: 86

You can use sub query.

example :

Select name,
(select count(*) from family_members where family_members.id = family_names.id) as count
FROM family_names;

Upvotes: 0

Ben
Ben

Reputation: 5198

Try:

Select family_names.name, count(*)
from family_names join family_members on family_names.name = family_members.name
group by family_names.name

(Note: I haven't tested this but I believe I have the syntax correct)

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

Could be you ca use a group by on inner join 

select family_names.name , count(*)
inner join family_members on family_names.id  = family_members. name_id
group by family_names;

Upvotes: 0

Related Questions