Showing data and counting from multiple databases in MySQL

Question

I have two tables, that is joined in some way. I've spent the last hour googling around, not finding any concrete answers that worked in my case.

The case is, table 1 is called Clients, and table 2 is called Projects. I need to list all the client names, followed by number of projects related to that project and the project title.

For example:

Client 1 (2 projects)
- Project 1 title
- Project 2 title

Client 2 (0 projects)

Client 3 (1 project)
- Project 1 title

How is this doable, in the simplest and easiest way?

Answer 1

You don't say anything about your app, but I'd recommend separating querying from displaying. Let SQL get the data for you and then let something else do presentation.

Answer 2

There are basically three parts to your query.

1. Joining the tables
2. Grouping the output by project
3. Counting the projects per client

The first is easy. It involves finding the client ID field in each table and use an JOIN clause specifying the two columns (one in each table) to correlate on. This will give you one row per project that also contains information for the matching client. This is almost what you're asking for.

It is tricky to put the second and third together in the one query and not one I would recommend. If you are going to be putting this in a program, then you can easily post-process the result from the query. To do this, you need to add an ORDER BY clause to specify sorting by client. That will put all the projects for each client in subsequent rows.

Now you can write a loop to process the output. As it does to, it has to watch for two things:

1. when the client ID changes
2. counting the projects

By doing this, it can easily display a "group header" for each client, and a "group footer" for the number of projects.