0

I have a problem in which I need to be able to count the number of activities a child has registered to do. At the moment I have this:

SELECT child_fname, activity_id
FROM Child
LEFT JOIN Activity
ON Activity.ActivityID=activity.activity_id
ORDER BY Child.child_fname;

and my tables are like this:

Child Table

  • child_firstname
  • child_secondname
  • child_age

Activity

  • activity_id
  • activity_name
  • activity_price

ChildActivity

  • child_id
  • activity_id

I'm looking for something like this

  • [Name][Number of Activities]
  • Sam Smith 5
  • Rachel Smith 4

I tried using an JOIN but my query doesn't update for new data

John Smith
  • 3
  • 1
  • Can you explain what you mean by the last sentence? Why does the given `JOIN` query not "update for new data"? – Nico Haase Jul 31 '18 at 14:29

2 Answers2

0

Use INNER JOIN

SELECT Child.child_firstname, COUNT(Activity.activity_id)
FROM Child
INNER JOIN Activity ON Activity.activity_id=ChildActivity.activity_id
INNER JOIN ChildActivity ON ChildActivity.child_id=Child.child_id
ORDER BY Child.child_firstname;

Ps: check column names and tables, please.

Example SQL Inner-join with 3 tables?

Roy Bogado
  • 4,299
  • 1
  • 15
  • 31
0

This is what I think you want

SELECT child_fname as Name, count(activity_id) as `Number of Activities`
FROM Child
LEFT JOIN ChildActivity
ON ChildActivity.child_id=Child.child_id
GROUP BY Child.child_id
ORDER BY COUNT(activity_id) DESC;

The point is that you want to join Child with ChildActivity and group by Child, so that, for each record in Child you get a count of activities.

Chris Lear
  • 6,592
  • 1
  • 18
  • 26
  • Ah! Sorry, yes, I do have a child_id which is a primary field in the Child table – John Smith Jul 31 '18 at 14:15
  • Yep this works! Do you know how to sort it by the person with the highest number of activities? I tried adding this GROUP BY ChildActivity.activity_id; But it added them all together (the children) as some have the same first name – John Smith Jul 31 '18 at 14:20
  • I've edited the query to include the order by clause – Chris Lear Jul 31 '18 at 14:23
  • Thank you Chris Lear! This works to perfection, I think I correctly marked your answer as the one. Thank you so much again! :) – John Smith Jul 31 '18 at 14:26