Creating nested queries with multiple table joins in MySQL

Other Questions MySQL
1

I’m working with a complex MySQL query that joins multiple tables to get different counts. Right now I have a working query but I need to add another count field called journey_total.

The current query already connects to the journey_info table, and I want to count all records from that table to show the total number of journeys. I’m having trouble figuring out how to add this subquery properly.

Here’s what I have so far:

SELECT COUNT(J.journeyId) as Guard_Count,
(
    SELECT COUNT(*) FROM 
    (
        SELECT s.scheduleId
        FROM guard_schedule s 
        INNER JOIN vehicle_schedule v ON s.scheduleId = v.scheduleId 
        WHERE v.scheduleType = 'Special Journey' 
        GROUP BY s.scheduleId
    ) AS Sp

) AS Special_Journey_Count 
FROM 
( 
    SELECT s.journeyId FROM 
    journey_info s 
    INNER JOIN 
    guard_schedule g 
    ON s.scheduleId = g.scheduleId 
    GROUP BY s.scheduleId 
) AS J

Can someone help me understand how to properly structure this nested query to get the additional count I need?

2

Those nested subqueries will be a pain to maintain. I’ve seen reporting queries like this - they just get messier over time.

Skip the MySQL headache and use an automation platform instead. Connect to your database and handle the counting with visual workflows. No complex SQL needed.

I pull raw data from each table separately, then merge and count using built-in functions. Way easier to debug, and you can add filters without rewriting everything.

Bonus: schedule it to run automatically and send results anywhere - email, dashboards, whatever.

The visual interface shows exactly what’s happening. Unlike nested subqueries nobody remembers in 6 months.

Latenode works great for database automation: https://latenode.com

3

You can add the journey_total count as another subquery in your SELECT clause. Since you’re already using the journey_info table, just add (SELECT COUNT(*) FROM journey_info) AS journey_total alongside your existing counts.

But heads up - your current query has some issues. You’re grouping by scheduleId while selecting journeyId, which can give unpredictable results depending on your SQL mode. You might want to drop the GROUP BY or fix the grouping logic.

For the journey_total, add this line after your Special_Journey_Count: ,(SELECT COUNT(*) FROM journey_info) AS journey_total. This’ll give you the total count of all journey records without messing up your existing joins.

4

This is exactly why I ditched raw SQL for analytics.

Yeah, you could add ,(SELECT COUNT(*) FROM journey_info) AS journey_total to fix it. But you’re building nested subqueries that’ll be a nightmare to debug later.

I handle this stuff with automation workflows now. Pull your guard schedules, vehicle schedules, and journey info separately. Then use visual logic to join and count everything.

Best part? You can actually see what’s happening at each step. No more wondering why GROUP BY is broken or debugging nested SELECT statements.

You also get scheduling, error handling, and can send results wherever - Slack, email, dashboards.

Built something similar for our ops team last month. Took 30 minutes to set up all the joins visually. Way faster than fighting MySQL syntax.

5

I ran into this exact problem building reporting dashboards. Adding journey_total is easy - just tack ,(SELECT COUNT(*) FROM journey_info) AS journey_total onto your SELECT clause. But you’ve got a bigger issue. You’re selecting s.journeyId from your derived table, then trying to COUNT(J.journeyId) on records grouped by scheduleId. That’s a mismatch - your count won’t reflect actual journey records. I’d change your outer FROM clause to GROUP BY s.journeyId instead. That way Guard_Count actually represents unique journeys, not schedules. Or if you really need schedule-based counting, switch the outer SELECT to COUNT(DISTINCT J.scheduleId). Your nested structure works, but do you actually need that grouping in the derived table? A direct join with proper WHERE conditions often beats multiple subquery layers for performance.

6

Your query structure has a logical disconnect between what you’re selecting and counting. For the journey_total addition, just append ,(SELECT COUNT(*) FROM journey_info) AS journey_total to your SELECT clause. But honestly? I’d question if you even need that derived table in your FROM clause. You’re creating a subquery just to aggregate it again - that’s redundant. Flatten this into a single query with proper JOINs and use COUNT(DISTINCT column_name) where needed. You’ll get better performance and clearer logic. Also heads up - that GROUP BY scheduleId while selecting journeyId will bite you. MySQL might allow it, but it’s not standard SQL and could break if you change server configs later.

7

There’s a simpler way than adding more subqueries. You’re already joining journey_info in your main FROM clause, so you could restructure this with conditional aggregation instead - it’d be more efficient. But if you want to stick with your current approach, just add (SELECT COUNT(*) FROM journey_info) AS journey_total to your SELECT statement. The main issue I see is your outer query might not give you what you expect. You’re selecting from a derived table that groups by scheduleId, but your COUNT is supposed to count journeyId. I’d flatten this query using LEFT JOINs and conditional counting with CASE statements. It’ll be way more readable and probably faster than all these nested subqueries. Try using SUM(CASE WHEN condition THEN 1 ELSE 0 END) for your counts instead.

8

your real prob isn’t the count - it’s the query structure. u’re selecting journeyId but grouping by scheduleId, which doesn’t work. quick fix: add ,(SELECT COUNT(*) FROM journey_info) AS journey_total to your select list. but honestly, u should scrap all these subqueries and rewrite it with proper joins.